cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

@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

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

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
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (21,154)