Skip to main content

Notifications

Power Automate - General Discussion
Answered

Using the dateDifference expression or ticks to find difference between two datetimes

Posted on 12 Jun 2023 21:48:10 by 6,512

This is a review of how to find the time difference between two datetimes. The two methods I know use different expressions which will have different formats of outputs/results. 

 

The dateDifference() expression - Output Result string d.hh:mm:ss.fffffff

 

 

dateDifference({StartDateTime},{EndDateTime})

 

 

Using ticks() expressions- Output Result is integer whole number

Days between datetimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),864000000000)

 

 

Hours between datetimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),36000000000)

 

 

Minutes between datetimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),600000000)

 

 

Seconds between datetimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),10000000)

 

 

 

Below I will describe:

  1. Expression dateDifference()
  2. Time calculation using the expression ticks()
    1. Expression ticks()
    2. Converting ticks into time values
    3. Difference between two datetimes using ticks expression
    4. Note about whole number and decimal results
  3. Summary

 

Expression dateDifference()

 

The use of the dateDifference() expression is straight forward with only needing 2 values in a single expression. The expression will use a start datetime and end datetime with a string output of d.hh:mm:ss.fffffff which shows the combined amount of days, hours, minutes, seconds, and fractional seconds between the two datetimes.

 

Link to expression info below:
https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#datedifference 

 

dateDifference() expressiondateDifference() expression

 

The expression below is what will be used:

 

 

dateDifference({start datetime},{end datetime})

 

 

Below is a flow example using a start datetime of May 1, 2023 12:00 PM and end datetime of May 4, 2023 11:30:15.45 AM which are put into separate variables (varStartTime and varEndTime). The variables are then used in an expression dateDifference(variables('varStartTime'),variables('varEndTime')) to find the time difference. The result difference output string is 2.23.30.15.4500000 where it is 2 days, 23 hours, 30 minutes, 15 seconds, and 45 hundredths of a second.

 

dateDifference() Expression SampledateDifference() Expression Sample

 

Below are some example output strings when using the dateDifference() expression.

Start DatetimeEnd DatetimedateDifference() output stringDescription
May 1, 2023 12:00 PMJanuary 1, 2029 5:30 PM 2072.05:30:002072 days. 5 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PMMay 31, 2023 12:00 PM30.00:00:0030 days. 0 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PMMay 31, 2023 12:00:00.1 PM30.00:00:00.100000030 days. 0 hours: 0 minutes: 0 seconds. 1 tenth fractional second
May 1, 2023 12:00 PMMay 4, 2023 10:00 PM3.10:00:003 days. 10 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PMMay 4, 2023 11:30 AM2.23:30:002 days. 23 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PMMay 2, 2023 10:00 PM1.10:00:001 day. 10 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PMMay 2, 2023 12:00 PM1.00:00:001 day. 0 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PMMay 2, 2023 11:30 AM23:30:0023 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PMMay 1, 2023 10:30 PM10:30:0010 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PMMay 1, 2023 12:30:35 PM00:30:350 hours: 30 minutes: 35 seconds
May 1, 2023 12:00 PMMay 1, 2023 12:00:35 PM00:00:350 hours: 0 minutes: 35 seconds
May 1, 2023 12:00 PMMay 1, 2023 12:00:00.08 PM00:00:00.08000000 hours: 0 minutes: 0 seconds. 8 hundredths fractional second
May 1, 2023 12:00 PMMay 1, 2023 12:00 PM00:00:000 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PMMay 1, 2023 10:30 AM-01:30:00minus 1 hour: 30 minutes: 0 seconds
May 1, 2023 12:00 PMMay 1, 2023 10:25:15 AM-01:34:45minus 1 hour: 34 minutes: 45 seconds
May 1, 2023 12:00 PMMay 1, 2023 10:20:05 AM-01:39:55minus 1 hour: 39 minutes: 55 seconds
May 1, 2023 12:00 PMApril 30, 2023 11:30 PM-12:30:00minus 12 hours: 30 minutes: 0 seconds
May 1, 2023 12:00 PMApril 30, 2023 12:00 PM-1.00:00:00minus 1 day. 0 hours: 0 minutes: 0 seconds
May 1, 2023 12:00 PMApril 30, 2023 10:30 AM-1.01:30:00minus 1 day. 1 hour: 30 minutes: 0 seconds

 

To use individual values from the output string of dateDifference() can be difficult due to the changing format and character count that is dependent upon the initial values.

dateDifference() Output String FormatDescription
d.hh:mm:ssEqual to 24 hours OR more than 24 hours difference between start and end datetimes
d.hh:mm:ss.fffffffEqual to 24 hours OR more than 24 hours difference between start and end datetimes; at least one of the datetimes contains fractional seconds
hh:mm:ssLess than 24 hours difference between start and end datetimes
hh:mm:ss.fffffffLess than 24 hours difference between start and end datetimes; at least on of the datetimes contains fractional seconds
-hh:mm:ssMinus less than 24 hours difference between start and end datetimes
-hh:mm:ss.fffffffMinus less than 24 hours difference between start and end datetimes; at least one of the datetimes contains fractional seconds
-d.hh:mm:ssMinus more than 24 hours difference between start and end datetimes
-d.hh:mm:ss.fffffffMinus more than 24 hours difference between start and end datetimes; at least one of the datetimes contains fractional seconds

 

In the two flow results from the photo below, I have a flow that is more than 24 hours difference between start and datetime, and a separate flow that is less than 24 hours difference between start and datetime. The output string of the dateDifference() expression has two different formats.

dateDifference() Results String Format ComparedateDifference() Results String Format Compare

 

To extract a specific hour or minute value using expressions such as split() or first() that finds special characters such as '.' period or ':' colon will require knowing if the result will have a day value or fractional seconds to format the expression correctly.

 

The dateDifference() expression is a good tool for getting the difference between two datetimes into a readable string.

 

Expression ticks()

 

The use of the ticks() expression will convert a datetime into ticks. Ticks are are 100-nanosecond intervals, since January 1, 0001 12:00:00 midnight to the datetime. Basically the number of nanoseconds from January 1, 0001 to the datetime divided by 100 is the number of ticks. The ticks can be converted into days, hours, minutes, seconds, and fractional seconds by using the appropriate conversion number to divide by.

 

A tick is equal to 100 nanoseconds, or one ten-millionth of a second, which is seven decimal places after a second (00.0000001).
https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#ticks 

Ticks ExpressionTicks Expression

The expression is written as: 

 

 

ticks({datetime})

 

 

The example flow below shows the ticks() expression used on a datetime of May 1, 2023 12:00 PM. The output result is the number of ticks that are between January 1, 0001 to May 1, 2023 12:00 PM. (This number of ticks would be the same as getting the number of nanoseconds between January 1, 0001 to May 1, 2023 12:00 PM and dividing that total amount of nanoseconds by 100.)

 

May 1, 2023 12:00 PM = 638,185,392,000,000,000 ticks 

There are 638 quadrillion, 185 trillion, 392 billion ticks between January 1, 0001 to May 1, 2023

 

Ticks Expression ExampleTicks Expression Example

 

Convert ticks into time values:

Knowing the datetime May 1, 2023 12:00 PM is the same as 638,185,392,000,000,000 ticks doesn't immediately help us much. The number is incredibly large and not in a normal term we would use. To get a better usage of the ticks number, we can convert it to something more understandable such as Days, Hours, Minutes, or Seconds by using a set conversion amount. (There is not a conversion amount in terms of years and months, both of which have a variable number of days.)

 

The table below shows how many ticks are within one of the corresponding time values. There are 864 billion ticks in a single day. There are 36 billion ticks in a single hour. If we consider there are 24 hours in a day and multiply the 36,000,000,000 ticks in a single hour by 24, the result is 864,000,000,000 which is the number of ticks in a single day.

Similarly, if we consider there are 60 minutes in an hour, we can divide the 36,000,000,000 ticks in a single hour by 60 the result is 600,000,000 which is the number of ticks in a single minute.

 

Time ValueTotal Ticks NumberTotal Ticks as IntegerDate Time Format d.hh:mm:ss.fffffff
1 Day = 864 billion ticks864,000,000,0008640000000001.hh:mm:ss.fffffff
1 Hour = 36 billion ticks36,000,000,00036000000000d.01:mm:ss.fffffff
1 Minute = 600 million ticks600,000,000600000000d.hh:01:ss.fffffff
1 Second = 10 million ticks10,000,00010000000d.hh:mm:01.fffffff
1 Decisecond = 1 million ticks1,000,0001000000d.hh:mm:00.1
1 Centisecond = 100 thousand ticks100,000100000d.hh:mm:00.01
1 Millisecond = 10 thousand ticks10,00010000d.hh:mm:00.001
1 Microsecond = 10 ticks1010d.hh:mm:00.000001
100 Nanoseconds = 1 tick11d.hh:mm:00.0000001
1 Nanosecond = 1/100 tick1/100 = 0.010.01d.hh:mm:00.000000001

 

In power automate you will use the div() expression to divide the number of ticks({DateTime}) by the conversion amount.

To Convert Ticks to Days divide by 864 billion:

 

 

div({ticks},864000000000)

 

 

 

To Convert Ticks to Hours divide by 36 billion:

 

 

div({ticks},36000000000)

 

 

To Convert Ticks to Minutes divide by 600 million:

 

 

 

div({ticks},600000000)

 

 

To Convert Ticks to Seconds divide by 10 million:

 

 

div({ticks},10000000)

 

 

Using the previous example to find the datetime May 1, 2023 12:00 PM is the same as 638,185,392,000,000,000 ticks. We can convert that tick quantity into different time values such as days, hours, minutes, or seconds. Remember this will be the total time difference between January 01, 0001 and May 1, 2023 12:00 PM. 

 

Below is an example of converting ticks to Days, Hours, Minutes, and Seconds.

Example Flow Ticks to Time ValueExample Flow Ticks to Time ValueExample Flow Ticks to Time Value ResultsExample Flow Ticks to Time Value Results

 

The output results show that the datetime May 1, 2023 12:00 PM is the same as 638,185,392,000,000,000 ticks. This number converted is the amount of time between May 1, 2023 12:00 PM and January 1, 0001 as days, hours, minutes, and seconds.

  • Days: There are 738,640 days between May 1, 2023 and January 1, 0001
  • Hours: There are 17,727,372 hours between May 1, 2023 and January 1, 0001
  • Minutes: There are 1,063,642,320 minutes between May 1, 2023 and January 1, 0001
  • Seconds: There are 63,818,539,200 seconds between May 1, 2023 and January 1, 0001

 

Time Difference Between Two DateTimes Using Ticks()

 

Most practical uses of ticks() will be to find the time difference between two datetimes. For example a request for an item is sent on May 1, 2023 at 12:00 PM and it was finally approved on May 4, 2023 at 11:30 AM and we want to know the amount of time the request was in process. We would convert both datetimes to ticks() and then subtract the Send (Start DateTime) from the Approval (End DateTime) to get the total difference of ticks between the two datetimes; then divide the difference in ticks by the conversion amount to get the total time difference as days, hours, or minutes.

 

In power automate we use the sub() subtract expression to subtract the number of ticks of the start datetime from the end date time. The End DateTime will have more ticks than the Start DateTime because it is further away from January 01, 0001. So the End DateTime should be the first (minuend) of the subtraction and the Start DateTime should be the second (subtrahend) of the subtraction EndDateTime - StartDateTime. Then divide this difference of ticks by the conversion time.

Subtraction Expression sub()Subtraction Expression sub()Divide Expression div()Divide Expression div()

 

Days Between DateTimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),864000000000)

 

 

Days Time Difference Using VariablesDays Time Difference Using Variables

 

Hours Between DateTimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),36000000000)

 

 

Hours Time Difference Using VariablesHours Time Difference Using Variables

 

Minutes Between DateTimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),600000000)

 

 

Minutes Time Difference Using VariablesMinutes Time Difference Using Variables

 

Seconds Between DateTimes using ticks:

 

 

div(sub(ticks({EndDateTime}),ticks({StartDateTime})),10000000)

 

 

Seconds Time Difference Using VariablesSeconds Time Difference Using Variables

 

Don't let the ticks() and nanoseconds references confuse you, this is basically simple Subtraction and Division.

Thought Example:

  • If Steve has 330 apples and John has 200 apples, to find out how many more apples Steve has than John you would subtract 330-200=130 apples. This is easy Subtraction.
  • If you had 130 minutes and wanted to convert it to hours. Since there are 60 minutes in an hour, you would divide the number of minutes 130 by 60 to get the total number of hours (Ex. 130 minutes / 60 = 2.16 hours). This is easy Division.

Now pretend the apples are actually datetime ticks(). Steve's apples will be ticks(End DateTime) and John's apples are ticks(Start DateTime). You subtract the two values to get the difference just like with the apples, except the values are ticks.

ticks(End DateTime) - ticks(Start DateTime) = TicksDifference

Then convert the TicksDifference into hours. Just like the easy division of converting minutes into hours, which is 60 minutes for 1 hour so Minutes/60 = Hours, you would use the number of ticks in 1 hour which is 36,000,000,000. So it will be TicksDifference/36,000,000,000 = Hours

 

Below is a flow example getting the total time values difference between two datetimes using ticks. The initialized variable varStartTime is the datetime May 1, 2023 12:00 PM. And the initialized variable varEndTime is May 4, 2023 11:30:15.45 AM.

Example Flow Time Difference Using TicksExample Flow Time Difference Using Ticks

 

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Days: Result is 2

Days Time Difference ExampleDays Time Difference Example

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Hours: Result is 71

Hours Time Difference ExampleHours Time Difference Example

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Minutes: Result is 4290

Minutes Time Difference ExampleMinutes Time Difference Example

 

Below is the time difference between May 1, 2023 at 12:00 PM and May 4, 2023 at 11:30 AM as Seconds: Result is 257415

Seconds Time Difference ExampleSeconds Time Difference Example

 

The output results show the total time values between May 1, 2023 12:00 PM and May 4, 2023 11:30:15.45 AM are as follows:

  • Total Days: 2
  • Total Hours: 71
  • Total Minutes: 4,290
  • Total Seconds: 257,415

 

Note About Whole Number Results and Decimal Results: 

The results of the previous expressions are whole number values and are not rounded. We know that the difference between the two datetimes is greater than 2 days, but since power automate div() expression does not add decimals if only using whole numbers, the output is only 2. To get a decimal value one or both of the factors in the div() expression needs to be a decimal which requires the use of the float() expression.

If we wrap each of the ticks({datetime}) values in a float() expression, it will return the results of each as a decimal value. That decimal value is then used in subtraction sub() expression which will also be decimal, and finally the division div() expression.

 

Days between DateTimes using ticks with decimal value output:

 

 

div(sub(float(ticks({EndDateTime})),float(ticks({StartDateTime}))),864000000000) 

 

 

 

Hours between DateTimes using ticks with decimal value output:

 

 

div(sub(float(ticks({EndDateTime})),float(ticks({StartDateTime}))),36000000000)

 

 

 

 

Here are the same result of days and hours as the above but including the float() expression.

Time Difference Ticks Decimal ResultsTime Difference Ticks Decimal Results

  

The decimal value output results show the total time values between May 1, 2023 12:00 PM and May 4, 2023 11:30:15.45 AM are as follows:

  • Total Days: 2.9793454860740742
  • Total Hours: 71.50429166577777

These outputs can be formatted further by using formatNumber() expression to determine a set amount of decimal places to return.

 

Summary

 

I know of two methods that will return the time value differences between two datetime values. The dateDifference() expression is a good tool that will return a string that is easier to read and understand. We can easily understand the dateDifference() string result of 2.23.30.15.45 as 2 days, 23 hours, 30 minutes, 15 seconds, and 45 hundredths of a second. However, this result is not as helpful if the time difference is needed in other steps to be added or calculated.

The use of the ticks() expression can seem complicated at first, but it is really just a way to get datetimes into a factor that can be calculated. When finding the difference between datetimes using the ticks() expression, the output will be a total time value that is either days, hours, minutes, or seconds as an integer. This value is easier to use in other steps where the time might need to be added or calculated with another value.

 

I hope this information helps or can be a reference, I will reply below with a way to extract values from the dateDifference() string.

  • GokhanA. Profile Picture
    GokhanA. 73 on 27 Jun 2024 at 14:04:49
    Re: Using the dateDifference expression or ticks to find difference between two datetimes

    great work! 

    many thanks

  • Holly_CMS Profile Picture
    Holly_CMS 523 on 13 Jun 2023 at 14:49:39
    Re: Using the dateDifference expression or ticks to find difference between two datetimes

    So AWESOME!!!!

     

    Thank you!!!!

  • Verified answer
    v-qiaqi@microsoft.com Profile Picture
    v-qiaqi@microsoft.com on 13 Jun 2023 at 04:31:51
    Re: Using the dateDifference expression or ticks to find difference between two datetimes

    Hi @wskinnermctc,

    So detailed tutorial.đź‘Ť

    Thanks for your sharing.

    Later, I will mark here to close this case so that other users who may have the similar issue could reach here dieectly.

    Thanks for your cooperation.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,168

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,851

Leaderboard

Featured topics