Calculate months between two dates!!!

[Zoran]

Hi,

I have a use case where I need to calculate number of months and days between two dates in a SharePoint list.

The result should be ex. 5,30 --- where first digit is months and the second is days

Below is the formula I found, but for some reason it's not accepted by SP.

=DATEDIF([StartDate]],[EndDate],"MD")

Does anyone have a suggestion how to do this?

BR /ZorroZZ

[Expiscornovus · Accepted solution]

Hi @Zoran,

For the calculation of the number of days you could use some sub the number of ticks of the start date from the end date. After that you can div it by to get the number of days.

Below is an example expression:

div(sub(ticks(variables('EndDate')), ticks(variables('StartDate'))),864000000000)

The months is a bit harder, since it hasn't got the same amount of days each month. A calendar month may contain 28 to 31 calendar days.

If you don't mind it is less accurate you can probably just divide the outcome of the first expression by the average, which I believe is 30.437 (including leap years etc.)

[msonmez]

Hello,

Instead of calculating days with ticks and converting them to months, I have used following approach: 

DATE 1: 31.12.2019

DATE 2: 16.11.2021

Expected difference result = 1 Years, 10 Months, 16 days = 22 Months (+16 days, which will be ignored)

1. I have started substracting years without thinking about the day and month. Year difference is 2021-2019 = 2 Years 
2. With the same thought, month difference is 11-12 = -1 Months 
3. And for the days, days difference = 16-31= -15 Days. It is only important, if the difference is less then 0 -> in this case 15-31 < 0.

My formula is =  (Month Difference) + (If Days Difference < 0 then -1 else 0) + 12*(Year Difference)

With my example, it will be (-1) + (-1) + 12*2 = 22 Months

You can implement this with multiple was in Power Automate, I have decided to use couple of Compose actions : 

Document Date Difference Days :

sub(int(formatDateTime(DATE2, 'dd')),int(formatDateTime(DATE1, 'dd')))

Document Date Difference Month

sub(sub(int(formatDateTime(DATE2, 'MM')),int(formatDateTime(DATE1, 'MM'))),if(lessOrEquals(int(outputs('Document_Date_Difference_Days')),0),1,0))

Document Date Difference Years

mul(sub(int(formatDateTime(DATE2, 'yyyy')),int(formatDateTime(DATE1, 'yyyy'))),12)

Date Difference Total Months

add(outputs('Document_Date_Difference_Years'),outputs('Document_Date_Difference_Month'))

[KK76875875]

This formulas are cool BUT, when I put data with 1yr difference, for example 01.03.2022 and 01.03.2023

Document Date Difference Month

sub(sub(int(formatDateTime(DATE2, 'MM')),int(formatDateTime(DATE1, 'MM'))),if(lessOrEquals(int(outputs('Document_Date_Difference_Days')),0),1,0))

It  return result -1, when it might return 0 (when it might be 12)

or if I put 01.03.2022 and 01.02.2023
It returns -2 value (when it might be 11)

[Ashwinreddy]

Hi msonmez,

That will be help full if i get to know what if i want the days also , as in your example it was ignored?

[MNowicki]

Hi,

I had similar challenge. I reviewed it and that really there are 3 possible scenarios to calculate:

1. You have two dates in the same year - then you need to subtract earlier month from later month
2. You have two dates year after year - then you need to calculate number of months in first year and in a second year
3. You have two dates and different in years is bigger then 1 - then you need to do the same like in point 2, plus number of months in each other year

I calculated it in a Compose step as if statement:

Checking difference in years:

sub(
    int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'yyyy')),
    int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'yyyy'))
)

if 0 then subtract months:

sub(
    int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'MM')),
    int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'MM'))
)

if 1 then calculate months in each year and add them together:

add(
  sub(
    12,
    int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'MM'))
  ),
  int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'MM'))
)

if more then 1 calculate months in first and last year, plus 12 month in each year in between:

add(
  add(
    sub(
      12,
      int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'MM'))
    ),
    int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'MM'))
  ),
  mul(
    12,
    sub(
      sub(
        int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'yyyy')),
        int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'yyyy'))
      ),
      1
    )
  )
)

Full expression:

if(
  equals(
    sub(
      int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'yyyy')),
      int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'yyyy'))
    ), 
    0
  ),
  sub(
    int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'MM')),
    int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'MM'))
  ),
  if(
    equals(
      sub(
        int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'yyyy')),
        int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'yyyy'))
      ), 
      1
    ),
    add(
      sub(
        12,
        int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'MM'))
      ),
      int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'MM'))
    ),
    add(
      add(
        sub(
          12,
          int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'MM'))
        ),
        int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'MM'))
      ),
      mul(
        12,
        sub(
          sub(
            int(formatDateTime(triggerOutputs()?['body/c9_enddate'], 'yyyy')),
            int(formatDateTime(triggerOutputs()?['body/c9_startdate'], 'yyyy'))
          ),
          1
        )
      )
    )
  )
)

Best Regards

[ucheuzor1]

@MNowicki , thanks so much for this solution. This covered different date scenarios that span across years. Thanks again for this optimal solution

[AnggieM]

Gracias! 

[AnggieM]

Gracias!!