# Adding "Months" to a Date (Column)

✭✭✭✭✭✭

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.

Tags:

• ✭✭✭✭✭

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 ) )

• ✭✭✭✭✭✭

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

• 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))

I hope this achieves the outcome you are looking for!

-Jessica

• 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

• ✭✭✭✭✭
edited 03/08/24

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)
)
```
• Amazing @Toufong Vang, thanks so much! I really appreciate it.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!