How to Count the Months Between 2 dates?

06/11/21
Accepted

What is the proper formula to use to count the months between the two dates?



Best Answer

  • Toufong VangToufong Vang ✭✭✭
    edited 06/11/21 Accepted Answer

    Hi, @ECD Auto Design .

    The following formula will get you the exact months.

    =(NETDAYS([Contract Start Date (Elliot)]@row, [End Date Based On Waterfall (Elliot)]@row)/365)*12

    Use...

    • = ROUND(xxx,0) for whole months
    • = ROUND(xxx,1) for months to 1 decimal place

    ...substituting the formula above for "xxx". (Remove the "=" from the original formula.)

    Why it works...

    • NETDAYS() returns the number of days between the start and end dates.
    • Dividing that number (days) by 365 converts your unit of measurement from days to years.
    • Multiplying the number of years by 12 converts the unit of measurement to months.

Answers

  • DMurphyDMurphy ✭✭✭✭✭
    edited 06/11/21

    @ECD Auto Design try:

    =MONTH([email protected]) - MONTH([Date 2]@row) where Date and Date 2 are the two columns with dates.

    But you probably need to account for changing years? Perhaps:

    =(YEAR([email protected]) - YEAR([Date 2]@row)) * 12 + (MONTH([email protected]) - MONTH([Date 2]@row))

    Check a few use cases to make sure that hangs together.

    dm

  • =(YEAR(Contract Start Date (Elliot)) - YEAR((End Date Based On Waterfall (Elliot))) * 12 + (MONTH(Contract Start Date (Elliot)) - MONTH(End Date Based On Waterfall (Elliot))


    That is what the formula looks like when i put my data into it. Now the only issue with it is it made it a negative number. I think its pretty close but not quite there yet



  • Toufong VangToufong Vang ✭✭✭
    edited 06/11/21 Accepted Answer

    Hi, @ECD Auto Design .

    The following formula will get you the exact months.

    =(NETDAYS([Contract Start Date (Elliot)]@row, [End Date Based On Waterfall (Elliot)]@row)/365)*12

    Use...

    • = ROUND(xxx,0) for whole months
    • = ROUND(xxx,1) for months to 1 decimal place

    ...substituting the formula above for "xxx". (Remove the "=" from the original formula.)

    Why it works...

    • NETDAYS() returns the number of days between the start and end dates.
    • Dividing that number (days) by 365 converts your unit of measurement from days to years.
    • Multiplying the number of years by 12 converts the unit of measurement to months.
  • @Toufong Vang That was the one! Thank you

Sign In or Register to comment.