You’re offline. This is a read only version of the page.
Announcements
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
Share
ReportdateDifference({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:
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() 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 Sample
Below are some example output strings when using the dateDifference() expression.
| Start Datetime | End Datetime | dateDifference() output string | Description |
| May 1, 2023 12:00 PM | January 1, 2029 5:30 PM | 2072.05:30:00 | 2072 days. 5 hours: 30 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 31, 2023 12:00 PM | 30.00:00:00 | 30 days. 0 hours: 0 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 31, 2023 12:00:00.1 PM | 30.00:00:00.1000000 | 30 days. 0 hours: 0 minutes: 0 seconds. 1 tenth fractional second |
| May 1, 2023 12:00 PM | May 4, 2023 10:00 PM | 3.10:00:00 | 3 days. 10 hours: 0 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 4, 2023 11:30 AM | 2.23:30:00 | 2 days. 23 hours: 30 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 2, 2023 10:00 PM | 1.10:00:00 | 1 day. 10 hours: 0 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 2, 2023 12:00 PM | 1.00:00:00 | 1 day. 0 hours: 0 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 2, 2023 11:30 AM | 23:30:00 | 23 hours: 30 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 1, 2023 10:30 PM | 10:30:00 | 10 hours: 30 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 1, 2023 12:30:35 PM | 00:30:35 | 0 hours: 30 minutes: 35 seconds |
| May 1, 2023 12:00 PM | May 1, 2023 12:00:35 PM | 00:00:35 | 0 hours: 0 minutes: 35 seconds |
| May 1, 2023 12:00 PM | May 1, 2023 12:00:00.08 PM | 00:00:00.0800000 | 0 hours: 0 minutes: 0 seconds. 8 hundredths fractional second |
| May 1, 2023 12:00 PM | May 1, 2023 12:00 PM | 00:00:00 | 0 hours: 0 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 1, 2023 10:30 AM | -01:30:00 | minus 1 hour: 30 minutes: 0 seconds |
| May 1, 2023 12:00 PM | May 1, 2023 10:25:15 AM | -01:34:45 | minus 1 hour: 34 minutes: 45 seconds |
| May 1, 2023 12:00 PM | May 1, 2023 10:20:05 AM | -01:39:55 | minus 1 hour: 39 minutes: 55 seconds |
| May 1, 2023 12:00 PM | April 30, 2023 11:30 PM | -12:30:00 | minus 12 hours: 30 minutes: 0 seconds |
| May 1, 2023 12:00 PM | April 30, 2023 12:00 PM | -1.00:00:00 | minus 1 day. 0 hours: 0 minutes: 0 seconds |
| May 1, 2023 12:00 PM | April 30, 2023 10:30 AM | -1.01:30:00 | minus 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 Format | Description |
| d.hh:mm:ss | Equal to 24 hours OR more than 24 hours difference between start and end datetimes |
| d.hh:mm:ss.fffffff | Equal 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:ss | Less than 24 hours difference between start and end datetimes |
| hh:mm:ss.fffffff | Less than 24 hours difference between start and end datetimes; at least on of the datetimes contains fractional seconds |
| -hh:mm:ss | Minus less than 24 hours difference between start and end datetimes |
| -hh:mm:ss.fffffff | Minus less than 24 hours difference between start and end datetimes; at least one of the datetimes contains fractional seconds |
| -d.hh:mm:ss | Minus more than 24 hours difference between start and end datetimes |
| -d.hh:mm:ss.fffffff | Minus 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 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.
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 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 Example
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 Value | Total Ticks Number | Total Ticks as Integer | Date Time Format d.hh:mm:ss.fffffff |
| 1 Day = 864 billion ticks | 864,000,000,000 | 864000000000 | 1.hh:mm:ss.fffffff |
| 1 Hour = 36 billion ticks | 36,000,000,000 | 36000000000 | d.01:mm:ss.fffffff |
| 1 Minute = 600 million ticks | 600,000,000 | 600000000 | d.hh:01:ss.fffffff |
| 1 Second = 10 million ticks | 10,000,000 | 10000000 | d.hh:mm:01.fffffff |
| 1 Decisecond = 1 million ticks | 1,000,000 | 1000000 | d.hh:mm:00.1 |
| 1 Centisecond = 100 thousand ticks | 100,000 | 100000 | d.hh:mm:00.01 |
| 1 Millisecond = 10 thousand ticks | 10,000 | 10000 | d.hh:mm:00.001 |
| 1 Microsecond = 10 ticks | 10 | 10 | d.hh:mm:00.000001 |
| 100 Nanoseconds = 1 tick | 1 | 1 | d.hh:mm:00.0000001 |
| 1 Nanosecond = 1/100 tick | 1/100 = 0.01 | 0.01 | d.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 Value
Example 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.
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()
Divide Expression div()
Days Between DateTimes using ticks:
div(sub(ticks({EndDateTime}),ticks({StartDateTime})),864000000000)
Days Time Difference Using Variables
Hours Between DateTimes using ticks:
div(sub(ticks({EndDateTime}),ticks({StartDateTime})),36000000000)
Hours Time Difference Using Variables
Minutes Between DateTimes using ticks:
div(sub(ticks({EndDateTime}),ticks({StartDateTime})),600000000)
Minutes Time Difference Using Variables
Seconds Between DateTimes using ticks:
div(sub(ticks({EndDateTime}),ticks({StartDateTime})),10000000)
Seconds Time Difference Using Variables
Don't let the ticks() and nanoseconds references confuse you, this is basically simple Subtraction and Division.
Thought Example:
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 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 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 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 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 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:
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 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:
These outputs can be formatted further by using formatNumber() expression to determine a set amount of decimal places to return.
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.
great work!
many thanks
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
All responses (
Answers (
