Months between two dates

Peppey

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!

Nick Korna

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:

Jeff Reisman

@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:

Peppey

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

Jeff Reisman

@Nick Korna

https://community.smartsheet.com/discussion/comment/344083#Comment_344083

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

Nick Korna

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:

Jeff Reisman

@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:

Peppey

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

Jeff Reisman

@Nick Korna

https://community.smartsheet.com/discussion/comment/344083#Comment_344083

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