Add four months to a Date
Hi,
I'm trying to get the first of the month that is four months after a specific date. 1/15/20 should return 5/1/20.
The date is in a column called Admit Date and I'm using the following formula:
=DATE(YEAR([Admit Date]@row), MONTH([Admit Date]@row + 120), 1)
However, when four months later is in 2021(Meaning, the admit date is in October or on..) the formula is not working!
Any suggestions?
Best Answers
-
Hey Rivky!
Have you tried using the function: =[Admit Date]@row + 120?
This should add 120 days to the target date and does not throw any sort of error when the admission is after October.
All the Best,
LK
-
I see I misread your post! You want the first of the month that is 4 months after the date in Admit Date! For that I think you would need something a bit more complex. This should work!
=DATE(YEAR([Admit Date]@row + 120), MONTH([Admit Date]@row + 120), 1)
Your formula was very close, but was not incrementing the year when it needed to!
Answers
-
Hey Rivky!
Have you tried using the function: =[Admit Date]@row + 120?
This should add 120 days to the target date and does not throw any sort of error when the admission is after October.
All the Best,
LK
-
I see I misread your post! You want the first of the month that is 4 months after the date in Admit Date! For that I think you would need something a bit more complex. This should work!
=DATE(YEAR([Admit Date]@row + 120), MONTH([Admit Date]@row + 120), 1)
Your formula was very close, but was not incrementing the year when it needed to!
-
Add an If statement checking if the date is from October and on.
Try the below:
=if(MONTH([Admit Date]@row)>9, DATE(YEAR([Admit Date]@row)+1, MONTH([Admit Date]@row + 120), 1),DATE(YEAR([Admit Date]@row), MONTH([Admit Date]@row + 120), 1))
-
Thanks, your idea with adding the 120 to the year and month worked! Thanks!
=DATE(YEAR([Admit Date]@row + 120), MONTH([Admit Date]@row + 120), 1)
-
Much better then mine. Thanks for the idea!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!