Adding "Months" to a Date (Column)

Susan Swisher

I have a Column [Planned FPI] 01/02/23 that I want to ADD a # of Months [Estimated Duration (Months] to and I can't get the formula to return a Date.

Toufong Vang

This formula will return the date you need:

=DATE(YEAR(((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row), MONTH(((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row), DAY([Planned FPI]@row))

Explanation

Given...

1 Year = 12 months

1 Year = 365 days

..there are 365/12 days in a month.

So 24 months from 11/9/2022 would be ((365/12)* 24 ) + (11/9/2022).

Using the column names from the sheet, the expression is...

((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row

However, 2024 is a leap year so this formula returns 11/8/2024 instead of 11/9/2024.

You'll have to force the "9" from the "Planned FPI" date into the calculation to get 11/9/2024.

DATE( YEAR(11/8/2024) , MONTH(11/8/2024) , DAY(11/9/2022) )

Substitute...

=DATE( YEAR( ((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row ) , MONTH( ((365 / 12) * [Estimated Duration (Months)]@row) + [Planned FPI]@row ) , DAY( [Planned FPI]@row ) )

Susan Swisher

@Toufong Vang THIS WAS PERFECT! amazing.. THANK YOU

Jessica Collins

Hi Susan,

This one is a little complicated but with the help of the MOD() function, we should be able to make this work. I am hoping the following function is formatted so you may just copy and paste it into your [Actual FPI] column but I am providing a pdf illustrating how this combination of functions work to help trouble shoot any issues you may have. 😊

Function:

=DATE(ROUNDDOWN(YEAR([Planned FPI]@row) + [Estimated Duration (Months)]@row / 12), MOD([Estimated Duration (Months)]@row, 12) + MONTH([Planned FPI]@row), DAY([Planned FPI]@row))

CalculatingEndDate_DurationInMonths.pdf

I hope this achieves the outcome you are looking for!

-Jessica

Mark McGrath

Hi all

I have tried using @Toufong Vang's formula but I'm not sure where I'm going wrong. I want to add the number of months from 4 columns to a date (Start Date). The answer I'm getting is 01/03/2016 rather than 02/08/2020. Any guidance would be appreciated.

Thanks

Mark

Toufong Vang

Hi, @Mark McGrath, my approach above does not work consistently. The following will work as intended. Give it a try. (Replace the temporary column names--COL1, COL2, ect.--with yours.)

=DATE(IF((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) >= 12, INT((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) / 12) + YEAR([Start Date]@row), YEAR([Start Date]@row)), MOD((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row), 12), DAY([Start Date]@row))

Here's an explanation of this approach.

The new expiry date is Z months away. Which can be expressed as:

MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row

That is to say...

Z = (MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row)

Using the DATE() function...

DATE(year , month , day)

or...

DATE( year
    , month
    , day
    )

year = IF( Z >= 12, INT(Z/12) + YEAR([Start Date]@row), YEAR([Start Date]@row))

month = MOD(Z,12)

day = DAY([Start Date]@row)

...or...

DATE( IF( Z >= 12, INT(Z/12) + YEAR([Start Date]@row), YEAR([Start Date]@row))
    , MOD(Z, 12)
    , DAY([Start Date]@row)
    )

...substitute for Z to complete...

DATE( IF((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) >= 12, INT((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row) / 12) + YEAR([Start Date]@row), YEAR([Start Date]@row))
    , MOD((MONTH([Start Date]@row) + [COL1]@row + [COL2]@row + [COL3]@row + [COL4]@row), 12)
    , DAY([Start Date]@row)
    )

Mark McGrath

Amazing @Toufong Vang, thanks so much! I really appreciate it.