Add four months to a Date

Automations 1

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?

Find more posts tagged with

Accepted answers

Lauren Kleitz

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,

Lauren Kleitz

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!

All comments

Lauren Kleitz

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,

Lauren Kleitz

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!

Leibel S

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

Automations 1

Hi @Lauren Kleitz

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)

Leibel S

https://community.smartsheet.com/discussion/comment/271600#Comment_271600

Much better then mine. Thanks for the idea!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.