cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillPage
Impactful Individual
Impactful Individual

Number of days between two Dates

I'm trying to calculate the number of days between two date/times. A bit of searching tells me there used to be a function for this, dateDiff() but it's now gone. Appears I'm not the only one who's noticed: https://powerusers.microsoft.com/t5/Flow-Ideas/Missing-Calculate-Number-of-days-between-two-Dates/id...

 

Despite this, I still need to calculate the number of days between two dates. I imagine there are a few ways of doing so. What's the easiest?

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

Hi @WillPage ,

 

Please try using the Expression below to calculate the difference between the two dates.

div(sub(ticks('2019-05-13'),ticks('2019-05-04')),864000000000)

Please check this blog and refer to the detailed solution:

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Flow-Calculate-Date-Diference/ba-p...

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-bacao-msft
Community Support
Community Support

Hi @WillPage ,

 

Please try using the Expression below to calculate the difference between the two dates.

div(sub(ticks('2019-05-13'),ticks('2019-05-04')),864000000000)

Please check this blog and refer to the detailed solution:

https://powerusers.microsoft.com/t5/Microsoft-Flow-Community-Blog/Flow-Calculate-Date-Diference/ba-p...

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

WillPage
Impactful Individual
Impactful Individual

@v-bacao-msftThanks. I found that thread after posting my question and indeed using ticks() solved the problem.

This is a nice formula.  We want to use it for employee leave forms.  We want the number of days calculated as working days only.  That means we exclude weekends.  Also, ideally, we would like to also exclude national holidays from this calculation.  Any ideas?

@pablocaron1745  I suggest, instead of using a formula with ticks() for this, use a Do Until loop.

 

Define a string variable called day. When you initialise it, set its starting value to be the start day of the leave in yyyy-MM-dd format. Next do a compose with the ending day in the same format.

 

Next initialise another variable, an integer called working days with a starting value of 0 or blank (null)

 

Now add an Until loop and the condition is the day variable is equal to the Compose (end date).

 

Within the Do Until, put a condition. The condition is formatDateTime(variables('day'),'dddd') starts with S.

 

In the Yes branch, do nothing. In the No branch, increment working days integer variable by 1.

 

Now, you will need a premium license for this (or pre-populate a SP list with the data) but you need to use the Calendarific API (it's free)

 

image.png

These formulas are:

formatDateTime(variables('day'),'dd')
formatDateTime(variables('day'),'MM')
formatDateTime(variables('day'),'yyyy')

Now another condition" length(body('Get_public_Holiday_today')?['response']?['holiday']) is equal to 0. In the Yes branch, add one to the working days variable using increment variable. In the No, do nothing.

 

Now add 1 minute delay (the free trier of calendarific only lets you do one API call per minute - there are workarounds if this is time sensitive).

 

Lastly, put variables('day') into a Compose called tempDay and next step, Set Variable action with the day variable to addDays(outputs('tempDay'),1,'yyyy-MM-dd).

Outside your Do Until you have an integer variable Working days with the number of working days in that period.

 

Workaround to calendarific API limitation: Do a one-off flow to get all the holidays in 1 year and put them in an SP list using the "iso" property of the response to fill a string column in SP. In your main flow, Get items with a Odata filter query for  iso eq '@{variables('day')}' and evaluate the length of the response. Calendarific docs here: https://calendarific.com/api-documentation

Anonymous
Not applicable

@WillPage can you show a graphic of the layout for string variable, compose and variable. I keep getting an error message. Thanks

jbrines
Post Prodigy
Post Prodigy

@v-bacao-msft 

 

I wonder if you could help me with my issue where I am calculating days or hours between a DateReported and Now (UK Time). The Days part is working but the hours isn't it is reporting so strange numbers or hours, if the hours go past a number of days it appears to work but I have seen it report 4 minutes as 7 hours.

 

I1.JPGI2.JPGI3.JPGI4.JPG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,175)