Calculate Full Months between two dates with Power Automate

My friend Antti Pajunen recently asked me whether it is possible to calculate the number of months between two dates with Power Automate, akin to the DateDif function in Excel. I like a challenge, and I’ve done a lot of experimenting with dates in Power Automate so this is the solution I came up with.

The Scenario

In this scenario Antti was working with Managed Services contracts which will always run for full months (i.e. if the Start Date and End Date were 9th January 2020 – 15th December 2020, the contract would run from 1st January 2020 – 31st December 2020) and we therefore need to be able to calculate the number of full months between those dates (in this case that would be 12 months).

The Solution

The solution to this is a single Compose action with the following expression:

int(first(split(string(add(div(add(div(sub(ticks(addDays(startOfMonth(addToTime(triggerBody()['date_1'], 1, 'Month')), -1)), ticks(startofmonth(triggerBody()['date']))), 864000000000),1),div(365.25,12)),0.5)),'.')))

As this expression is lengthy and contains quite a few different functions I’ve broken it out below:

1. Compose Start of Month for Start Date – to get the start of the month for the start date we just use the startOfMonth function.

2. Compose End of Month for End Date – as there is no endOfMonth function in Power Automate we have to get slightly more creative to calculate the end of month. For my expression I add one month to the End Date, then use the startOfMonth function to get the 1st day of that month, then subtract one day to get the end of month date.

3. Compose DiffinDays – now that we have the proper Start Date and End Date we want to work with we need to calculate the total number of days between the dates. There is no datediff function in Power Automate, so we need to convert them to their ticks representation, then subtract the start date value from the end date value, then divide the result by 864000000000 and then add 1 to get the total number of days between the values. This can be seen below:

ticks value for End Date of 2020-12-31 = 637449696000000000

ticks value for Start Date of 2020-01-01 = 637134336000000000

637449696000000000 – 637134336000000000 = 315360000000000

315360000000000 / 864000000000 = 365

365 +1 = 366

4. Compose DiffinMonths – now that we have the total number of Days between the two dates we need to calculate the total number of months. In order to do that we divide the resulting value from above by 365.25/12. We use 365.25 to account for leap years, and divide 365.25 by 12 to get an average value for months. Using the example above:

366/(365/12) = 12.0246406

5. Compose DiffinMonth Round – the final step is to round the value returned from the step above to the closest integer. In order to do this we need to add 0.5 to the value as the rounding calculation always rounds down. We then convert the float value to a string, split it on the decimal point, take the first part of the value (i.e. the integer) and convert that back to an integer value.

The result from above would then convert 12.0246406 to 12, showing that there are 12 full months between 2020-01-01 – 2020-12-31.

Conclusion

To make this flow more efficient we combine all of the steps above into a single Compose action with the full expression:

If I’m honest, this is quite a long expression for what should be a relatively simple calculation, but in my testing it has worked consistently. I’d love to hear from others in the community if they find this useful or if they know of a more elegant solution. Drop me a comment below with your thoughts.

If you’d like to download a copy of this solution for yourself then click here.

Original Post https://ryanmaclean365.com/2020/10/14/calculate-full-months-between-two-dates-with-power-automate/