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:
- Expression dateDifference()
- Time calculation using the expression ticks()
- Expression ticks()
- Converting ticks into time values
- Difference between two datetimes using ticks expression
- Note about whole number and decimal results
- 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#...
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.
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.
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#...
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
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 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.
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.
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)
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.
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
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
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
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
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.
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.
Accepted Solutions
Helpful resources
@LaurensM is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024. Consistent Engagement: He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges. Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone. Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community. Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community! Keep up the fantastic work!
We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience! Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up? Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen. Don't miss your moment--start submitting in the Copilot Cookbook Gallery today! Thank you, Engagement Team
We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps. Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community. We can't wait to see what you "cook" up!
You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun. Now that you are a member, you can enjoy the following resources: Welcome to the Community News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate. Be sure to subscribe to this board and not miss an announcement. Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows. You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook. Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today. Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome. Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together. Power Automate Community Team
Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp
It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community. As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn! Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report. Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone. Thank you for helping us keep the community clean and useful!
