cancel
Showing results for
Did you mean:
Solution Sage

## 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

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.
7 REPLIES 7
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.
Solution Sage

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

New Member

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?

Solution Sage

@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)

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

Post Prodigy

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.

Helper I

can you show the steps in power automate flow? I don't get what to put in do until condition.

Announcements

#### Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

#### Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

#### Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,836)