Date Difference in Power Automate

#powerautomate #powerplatform

I’m sure the ticks trick (no pun intended), used for calculating the difference between 2 timestamps, have come in handy for us Power Automate-tors. However, Power Automate has recently been updated with a dateDifference function!

With this function, you can get the difference in days as well as the hours, minutes and seconds remaining. This function provides the results in a “timespan”.

dateDifference as defined in the reference guide

Return the difference between two timestamps as a timespan. This function subtracts startDate from endDate and returns the result as timestamp in string format.

dateDifference('<startDate>', '<endDate>').

So, a timespan is a special type of timestamp that represents a duration in time. I.e., in the case of dateDifference, it is the duration of time between the startDate and endDate.

For example,

 dateDifference(‘2022-11-10’, ‘2023-11-10’)         

will result in “365.00:00:00”.

Let’s take a deeper look at the result

It’s important to note that the remaining hours represent what’s left after the duration in days are calculated and the remaining minutes represent the minutes left after the remaining hours are calculated and so on.

If the duration of time between the 2 dates is less than a day, the day part is left out of the result.?

E.g.

dateDifference(parseDateTime('10/11/2022 09:10:24','en-gb'),parseDateTime('10/11/2022 09:28:44','en-gb'))        

will result in “00:18:20”. I.e., 0 days, 0 hours, 18 minutes and 20 seconds duration.

Now let’s look at the flow

First, we create timestamps for our startDateTime and endDateTime

Then, we use the dateDifference function to calculate the difference between our 2 timestamps

This will result in

Next, we need to figure out how to extract all the useful information from the result. We do this by using the split function to break-up the resulting string timespan. We will take advantage of the column characters in the string, using them as our delimiter.

Finally, we build a custom object to hold all our timespan information

This object contains some expressions to calculate the days, hours left etc. Note splitDateDifferenceOutput represents the result of the previous split operation.

Expression to extract secondsLeft (remaining seconds):

int(outputs('splitDateDifferenceOutput')[2])        

The secondsLeft is the 3rd item in the returned array of the split operation. Therefore, this can be accessed with index number 2

Expression to extract minutesLeft (remaining minutes):

?int(outputs('splitDateDifferenceOutput')[1])        

The minutesLeft is the 2nd item in the returned array of the split operation. Therefore, this can be accessed with index number 1

Expression to extract hoursLeft (remaining hours):

?int(
????? last(
???????????? split(outputs('splitDateDifferenceOutput')[0],'.')
????? )
)        

The hoursLeft and the days information (sometimes) makes up the first item in the split result. If the timespan is greater than a day, then the duration in days will be included along with the hours left but will be separated by a period. E.g. a duration of 10 days, 5 hours will be “10.05”. So, we can split the part further with a period as the delimiter.

We will have a result that looks like this for greater than 1 day time span:

[“10”, “05”]

…and a result like this in less than 1 day time span:

[“05”] which just represents the hours.

Therefore, in either case, the last item in the array will always represent the hours.

Expression to extract days:

if(

??? greater(

??????? indexOf(outputs('splitDateDifferenceOutput')[0],'.'),

??????? -1

??? ),

??? int(split(outputs('splitDateDifferenceOutput')[0],'.')[0]),

??? 0

)        

Here, we check if a period character exists in the 1st item from the earlier split operation. If a period character is found, i.e., indexOf results in a number greater than -1, then, the day value is the first item from the split operation, on the first item of the previous split operation (whew! I know... a mouth full ??). Else, just return zero.

Whew! I no that was a long one but if you wanted to calculate the time span in say minutes, then note that you can’t just use the remaining minutes output. To achieve this, you need to:

1.??????Convert all the other data points to minutes

• ?????Days to minutes: days * 24 * 60
• ?????Hours to minutes: hoursLeft * 60
• ????Seconds to minutes: secondsLeft / 60

2.??????Add all the results with the remaining minutes

You can download the flow here