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.
Answers
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!