Calculate # of months between two dates that cross over years

Amy Evans2

Hello,

How would you calculate the number of months in a situation where the dates cross over years? See screenshot.

Can the same formula also be used if Target and Start Dates are within the same year?

Thanks,

AE

Paul Newcome

The most consistent way to do this (that I have found) is to create a number that reflects yyyymm and then subtract one from the other.

To create the number we first pull the year

=YEAR([Target End]@row)

Then we need to convert it to a text so that the month numbers are attached to the end instead of added.

=YEAR([Target End]@row) + ""

Next we need to pull the two digit month number and tack it on.

=YEAR([Target End]@row) + "" + LEFT([Target End]@row, 2)

Now we need to convert this 6 digit text string into a numerical value.

=VALUE(YEAR([Target End]@row) + "" + LEFT([Target End]@row, 2))

Now that we have our [Target End], we can do the same thing for the [Target Start] and subtract that from the end.

=VALUE(YEAR([Target End]@row) + "" + LEFT([Target End]@row, 2)) - VALUE(YEAR([Target Start]@row) + "" + LEFT([Target Start]@row, 2))

And that should do it for you.

Amy Evans2

Paul,

Thanks for writing it step by step! Really helpful, but I'm unclear about how the following will give me the correct number of months in the yyyymm format:

Example:

Target End Date: 202105

Target Start Date: 202007

I must be misunderstanding, because if I subtract the Start Date from the End Date (202105 - 202007), I get 98.

I'd like to end up with "10" as the number of months between the two dates.

Sorry, if I misunderstood your wonderful write-up. :)

AE

Paul Newcome

No. You are absolutely correct. I got my posts mixed up. My apologies.

Try this...

=(12 - MONTH([Target Start Date]@row)) + MONTH([Target End Date]@row) + (12 * (YEAR([Target End Date]@row) - YEAR([Target Start Date]@row) - 1))

Subtracting the starting month from 12 tells us how many months were left within the start year.

Then we add the month number from the end date.

Then we take the end year minus the start year and subtract 1 from that. Multiplying that number by 12 will give us 12 months for ever year more than 1 that is in between the start and end.

Once we add all of that together, we get your answer.

Here are a few examples:

1 Jan 20 - 1 Feb 20

Subtract Starting month from 12 and we get 11. Add the ending month number and we get 13. Subtract the start year from the end year (0) and subtract 1 from that (-1) then multiply by 12 (-12) and add it to the month calculations to get

13 + (-12) = 1

1 Jan 20 - 1 Feb 21

Same for the months to where they total 13. Then we do 2021 - 2020 - 1 to get 0. 0 times 12 equals 0.

13 + 0 = 13

1 Jan 20 - 1 Feb 2022

Same for the months to get 13. Then 2022 - 2020 - 1 gives us 1. 1 * 12 = 12

13 + 12 = 25

I hope that logic makes sense. Let me know if you would like me to explain it a different way.

Either way... That should get the job done for you.