How to compare dates and return the difference between them?

Hi Team,

How can I compare the Date & Time with Current time and return the difference is Hrs?
From the attached Tickets.xlsx (9.8 KB)
file trying to fetch only record which is Opend within last 24hrs .

Thanks,
Aby

You can try this…

Define a variable with type System.DateTime.
Read your excel file with Excel Application Scope and Read range (Range " "), then store it as a Datatable output.
From Column 1, loop through each row store it as another variable
Add an IF loop to check if variable1.tostring equals variable2.tostring.
Check this post how to change variables in DateTime format

2 Likes

Hi.

Normally, you would use a Timespan which will be in the format of 00:00:00.000 or 00:00:00

So, you need to just do a subtraction between 2 datetime types.
For example,

Now - dtvariable

This will take the difference, then you compare that to a timespan.
So, if you declare a Timespan type variable and set it to 05:00:00 (which is 5 hours)
Then, the condition:
Now - dtvariable <= timeVariable
It will say if Now minus other date time is less than or equal to timespan

Hopefully, that makes sense.

Regards.

1 Like

I made a mistake 5 hours would be 05:00:00

Here is more info on how to use Timespans:
https://www.dotnetperls.com/timespan

1 Like

@ClaytonM and @kpanse110

Thanks for the reply. Still I quite don’t understand your answers.
I think it looks like the date sting in the input file is not in proper date format. Please see the attached work flowDateDiff.xaml (7.0 KB)
And the input file is Tickets.xlsx (9.8 KB) its CSV file only, as CSV upload won’t support in this site, I am uploading as xlsx

"Its throwing the error “String was not recognized as a valid DateTime” also tried following the thread here but same error. Would be great if you could just go through the same and assist.

Hi. I’m not on a computer with Studio. Can you show the code that is causing the error?
Essentially, you need to convert the string to a date time value, and there are a couple ways to do this. Additionally, you need to make sure the string is a date before conversion.

Let’s take this simple example:

...assuming str = "1/23/2019"
Assign d <As DateTime> = CDate(str)

Now, that would convert the string to a date time value.
You can also skip the extra assignment and convert it directly in your comparison:

...assuming str = "1/23/2019"
Condition: Now - CDate(str) <= timespanVariable

However, we are missing the check to make sure the string is a date prior to conversion.
You can do this inline (less clutter) or in another If activity:
The inline example would look like this:

...assuming str = "1/23/2019"
Condition: If(IsDate(str.Trim), Now - CDate(str.Trim) <= timespanVariable, False)

Note: CDate() is the same as Convert.ToDate()
Reminder: You can use IsDate() in an outer If activity if you prefer.

There are other .NET methods for Date Time management. These are .Parse(), .ParseExact(),.TryParse() and .TryParseExact(). .Parse() and .TryParse() are basically the same as using the vb CDate() and IsDate(), which requires the date to be in a specific format. When the format is different or in a different culture, then that’s when .ParseExact() and .TryParseExact() become useful. Here are some examples of these:
https://www.dotnetperls.com/datetime-tryparse

Like I said, these are useful if the date is in a format where you need to specify the format, such as “dd/MM/yyyy” where the day is before the month.

...assuming str = "23/01/2019"
Condition: If(DateTime.TryParseExact(str, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture), Now - DateTime.ParseExact(str, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) <= timespanVariable, False)

Also, you can try searching for more example on the forums.

Apologies, if I make any syntax mistakes.

Hope this helps. Regards.

2 Likes

Hi @abyvarghes,

You can find the date difference with ‘timespan’, like @ClaytonM said above.

First you need to convert the the 2 dates of each row from string to datetime format.
Let ‘date1’ & ‘date2’ be the ‘DateTime’ variables. Inside the for each row activity,
Assign date1=Datetime.ParseExact(row("Opend Date and Time").ToString.trim,"MM/dd/yyyy HH:mm:ss",system.Globalization.CultureInfo.InvariantCulture)
and
date2=Datetime.ParseExact(row("Laste Updated date and Time").ToString,"MM/dd/yyyy HH:mm:ss",system.Globalization.CultureInfo.InvariantCulture)

Create a varible with ‘Timespan’ type. Let it be ‘TimeDiff’.
Assign TimeDiff=date2- date1
You can get the time difference in days, hours,minutes,seconds… by using TimeDiff.TotalDays, TimeDiff.TotalHours etc.

This may be due to the issue with your input. Please check for unnecessary blank space or invalid characters.

Please have a look at at the workflow I’ve created to compare the 2 dates and filter records which have a time difference of more than 1 day. Filter_with_hours.xaml (11.0 KB)

*I used the date format as “MM/dd/yyyy HH:mm:ss”. You can change it as per your input.:grinning:

Warm regards,
Nimin

1 Like

Thanks a lot @ClaytonM and @nimin. And very sorry for my late response.

1 Like

Hello @abyvarghes please check this i think it answers your query in a easy way

Main.xaml (15.6 KB)