Date time condition

image
Hi everyone can someone please help getting my problem done. I have column “DATE_TIME” with string format ("yyMMddHHmm). I have a condition to fill my “DATE_BOOK” column :
if HH:mm >= 10:15 AND ddd != “Friday” -------------- AddDays(1)
else if HH:mm <= 10:15 -------------- just print the same string as(“MMdd”)
else -------------- AddDays(3)
can someone solve this? sorry for bad english

Hi @Kevin_Maurice

  1. Use a For Each Row activity to loop through each row in your data table.
  2. Inside the loop, use the DateTime.ParseExact method to convert the “DATE_TIME” string to a DateTime object. You can specify the format string as “yyMMddHHmm”.
  3. Use the DateTime object’s properties to get the day of the week and the hour/minute values.
  4. Use an If statement to check the conditions you described: a. If the time is greater than or equal to 10:15 and the day is not Friday, add one day to the DateTime object using the AddDays method. b. If the time is less than or equal to 10:15, use the ToString method to format the DateTime object as “MMdd” and assign the resulting string to the “DATE_BOOK” column. c. Otherwise, add three days to the DateTime object using the AddDays method.
  5. Use the ToString method again to format the DateTime object as “MMdd” and assign the resulting string to the “DATE_BOOK” column.
For Each row As DataRow In dataTable.Rows
    Dim dateTimeStr As String = row("DATE_TIME").ToString()
    Dim dateTime As DateTime = DateTime.ParseExact(dateTimeStr, "yyMMddHHmm", CultureInfo.InvariantCulture)
    Dim dayOfWeek As DayOfWeek = dateTime.DayOfWeek
    Dim hour As Integer = dateTime.Hour
    Dim minute As Integer = dateTime.Minute
    
    If (hour >= 10 AndAlso minute >= 15) AndAlso (dayOfWeek <> DayOfWeek.Friday) Then
        dateTime = dateTime.AddDays(1)
    ElseIf hour <= 10 AndAlso minute <= 15 Then
        row("DATE_BOOK") = dateTime.ToString("MMdd")
        Continue For
    Else
        dateTime = dateTime.AddDays(3)
    End If
    
    row("DATE_BOOK") = dateTime.ToString("MMdd")
Next
1 Like

@Kevin_Maurice

Please follow

  1. Read the data using read range into datatable dt
  2. Use for each row in datatable dt
  3. Use a assign activity with datetime variable dat on left and this expression on toght datetime.parseexact(currentrow("DATE_TIME").ToString,"yyMMddhhmm",System.Globalization.CultureInfo.InvariantCulture) - this will convert string to datetime format
  4. After that use a if condition (dat.hour > 10 OrElse (dat.hour=10 And dat.minute >15)) AndAlso Not dat.ToString("dddd").ToLower.Equals("friday")
  5. On then side you have to use adsign with currentrow("Date_Time") = dat.Adddays(1).ToString("yyMMddhhmm")
  6. On else sode use another if condition with dat.hour < 10 OrElse (dat.hour=10 And dat.minute <=15)
  7. on else side use currentrow("Date_Time") = dat.Adddays(3).ToString("yyMMddhhmm")
  8. On then side log message with dat.ToString(" MMdd")

Hope this helps

Cheers

1 Like

Hello @Kevin_Maurice
Initially Convert the String into Date Time Format using

  1. Use read range activity and store data as DT
  2. Use For Each loop data table. and try below conditions
DateTime.ParseExact(CurrentRow(0).tostring.trim,"yyMMddHHmm",System.Globalization.CultureInfo.InvariantCulture())

If Condition

(DateTime.ParseExact(CurrentRow(0).tostring.trim,"yyMMddHHmm",System.Globalization.CultureInfo.InvariantCulture()).TimeOfDay>DateTime.ParseExact("10:15","HH:mm",System.Globalization.CultureInfo.InvariantCulture()).TimeOfDay) AND Not DateTime.ParseExact(CurrentRow(0).tostring.trim,"yyMMddHHmm",System.Globalization.CultureInfo.InvariantCulture()).ToString("dddd").ToUpper.Contains("FRIDAY")
CurrentRow(1)=DateTime.ParseExact(CurrentRow(0).tostring.trim,"yyMMddHHmm",System.Globalization.CultureInfo.InvariantCulture()).AddDays(1).ToString("yyMMddHHmm")

Place If condition On Else

DateTime.ParseExact(CurrentRow(0).tostring.trim,"yyMMddHHmm",System.Globalization.CultureInfo.InvariantCulture()).TimeOfDay<DateTime.ParseExact("10:15","HH:mm",System.Globalization.CultureInfo.InvariantCulture()).TimeOfDay)
CurrentRow(1)=DateTime.ParseExact(CurrentRow(0).tostring.trim,"yyMMddHHmm",System.Globalization.CultureInfo.InvariantCulture()).AddDays(1).ToString("MMdd")
CurrentRow(1)=DateTime.ParseExact(CurrentRow(0).tostring.trim,"yyMMddHHmm",System.Globalization.CultureInfo.InvariantCulture()).AddDays(3).ToString("MMdd")
1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.