# Months between two dates

✭✭
edited 09/21/22

Hey everyone! I am hoping you can help me. I am trying to calculate the number of months between a date and today. For example, if the date is 01/01/22 and today is 09/20/22 I need a formula to show 8 months.

The problem I am having is some of the beginning dates are in another year. Based on the formula I currently am using I am receiving negative months. An example of this is starting date is 12/13/21 and based on today's date (9/20/22) I am getting -3 months.

The formula I am using is MONTH(TODAY())-[Start Date]

Any help would be appreciated!

• ✭✭✭✭✭✭

Hi @Peppey,

I think the below will do what you're after:

=((YEAR(TODAY()) - YEAR([Start Date]@row)) * 12) + MONTH(TODAY()) - MONTH([Start Date]@row)

This will give you negatives if the start date is in the future, but it functions fine for times in the past:

• ✭✭✭✭✭✭

@Peppey You'll need to use some IFs to account for different years.

=IF(AND(YEAR(TODAY()) - YEAR([Start Date]@row) >= 1, MONTH([Start Date]@row) >= MONTH(TODAY())), ((YEAR(TODAY()) - YEAR([Start Date]@row)) * 12 - (MONTH([Start Date]@row) - MONTH(TODAY()))), IF(AND(YEAR(TODAY()) - YEAR([Start Date]@row) >= 1, MONTH([Start Date]@row) < MONTH(TODAY())), (MONTH(TODAY()) - MONTH([Start Date]@row) + ((YEAR(TODAY()) - YEAR([Start Date]@row)) * 12)), (MONTH(TODAY()) - MONTH([Start Date]@row))))

In English:

If this year minus the Start Date year is >= 1, AND the Start Date month is >= this month, then count the number of years and multiply by 12 and subtract the result of the start date month minus the current month. If this year minus the Start Date year is >= 1, AND the Start Date month is < this month, then subtract the start date month from this month, and add that result to the result of the count of the number of years multiplied by 12; otherwise (i.e. today and the start date are in the same year,) just subtract the start month from the current month.

Results:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭

Hi Jeff and Nick! You both are awesome! Thank you so much.

• ✭✭✭✭✭✭

@Nick Korna

I think I over-engineered my answer. Nice work!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

Hi @Peppey,

I think the below will do what you're after:

=((YEAR(TODAY()) - YEAR([Start Date]@row)) * 12) + MONTH(TODAY()) - MONTH([Start Date]@row)

This will give you negatives if the start date is in the future, but it functions fine for times in the past:

• ✭✭✭✭✭✭

@Peppey You'll need to use some IFs to account for different years.

=IF(AND(YEAR(TODAY()) - YEAR([Start Date]@row) >= 1, MONTH([Start Date]@row) >= MONTH(TODAY())), ((YEAR(TODAY()) - YEAR([Start Date]@row)) * 12 - (MONTH([Start Date]@row) - MONTH(TODAY()))), IF(AND(YEAR(TODAY()) - YEAR([Start Date]@row) >= 1, MONTH([Start Date]@row) < MONTH(TODAY())), (MONTH(TODAY()) - MONTH([Start Date]@row) + ((YEAR(TODAY()) - YEAR([Start Date]@row)) * 12)), (MONTH(TODAY()) - MONTH([Start Date]@row))))

In English:

If this year minus the Start Date year is >= 1, AND the Start Date month is >= this month, then count the number of years and multiply by 12 and subtract the result of the start date month minus the current month. If this year minus the Start Date year is >= 1, AND the Start Date month is < this month, then subtract the start date month from this month, and add that result to the result of the count of the number of years multiplied by 12; otherwise (i.e. today and the start date are in the same year,) just subtract the start month from the current month.

Results:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭

Hi Jeff and Nick! You both are awesome! Thank you so much.

• ✭✭✭✭✭✭

@Nick Korna

I think I over-engineered my answer. Nice work!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!