Number of days between two Dates

[WillPage]

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?

[v-bacao-msft · Accepted solution]

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,

[WillPage]

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

[pablocaron1745]

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?

[WillPage]

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

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

[jbrines]

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

[Eyfa]

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

[rohrbacker2]

This works, thank you!!!

[MitheshRpa]

can i have this in desktop flow?