EDATE equivalent in Smartsheet - IFERROR(DATE(YEAR....

I found some examples in previous discussions and tried it

=IFERROR(DATE(YEAR([Sale Month]@row), MONTH([Sale Month]@row) + 1, DAY([Sale Month]@row)), DATE(YEAR([Sale Month]@row) + 1, 1, DAY([Sale Month]@row)))

my theory was if Dec 1 was +1, then Jan would be +2 etc….it seemed to work until Feb onwards it keeps returning Jan 2025??

Find more posts tagged with

Accepted answers

All comments

dojones

I think your formula is returning as an error and then defaulting to Jan because this part of formula is hard coded to Jan.

DATE(YEAR([Sale Month]@row) + 1, 1, DAY([Sale Month]@row))

Maybe a simpler approach would be to just add 30, 60, or 90 days.

=[Sale Month]@row + 90

Tanya B

That does not return 1st of the month as not every month has 30days that's why I am trying this route.

You state that Jan is hard coded, however you can see below it returns a December date (formula for that cell below)

Carson Penticuff

Here is a link to a similar question.

https://community.smartsheet.com/discussion/109494/add-months-to-date-formula

Be warned, adding months in Smartsheet is much more complex than Excel. Give this thread a read and I can help afterwards if it is confusing. (spoiler alert, it is confusing)

Pinging a couple of people that provided excellent solutions toward the end of that thread.

@VirgilT @brianschmidt

dojones

I'm suggesting that you are getting an error which is defaulting to the error condition.

If you want it to always return the 1st day of month, try this:

30 Day Excercise - (30):

=IF(MONTH([Sale Month]@row) + 1 = 13, "1/01/" + (YEAR([Sale Month]@row) + 1), (MONTH([Sale Month]@row) + 1) + "/01/" + YEAR([Sale Month]@row))

30 Day Excercise - (60):

=IF(MONTH([Sale Month]@row) + 1 = 13, "2/01/" + (YEAR([Sale Month]@row) + 1), IF(MONTH([Sale Month]@row) + 2 = 13, "1/01/" + (YEAR([Sale Month]@row) + 1), (MONTH([Sale Month]@row) + 2) + "/01/" + YEAR([Sale Month]@row)))

30 Day Excercise - (90):

=IF(MONTH([Sale Month]@row) + 3 = 15, "3/01/" + (YEAR([Sale Month]@row) + 1), IF(MONTH([Sale Month]@row) + 3 = 14, "2/01/" + (YEAR([Sale Month]@row) + 1), IF(MONTH([Sale Month]@row) + 3 = 13, "1/01/" + (YEAR([Sale Month]@row) + 1), IF(MONTH([Sale Month]@row) + 3 = 13, "1/01/" + (YEAR([Sale Month]@row) + 1), (MONTH([Sale Month]@row) + 3) + "/01/" + YEAR([Sale Month]@row)))))

This works through 90 days. Extending to 120 and beyond requires adding more IF similar statements.

Another way to consider is creating a table with the dates and then using VLookup or Index Match to populate the 30, 60, 90, etc It depends on how far you are planning to extend because the formula gets longer with each month.

Tanya B

couple things….

where is Smartsheet with the EDATE equivalent?

2. format will not work in those cells now

3. should the second formula be 14 (see purple square)

Paul Newcome

I had a pretty complex formula for an EDATE replica, but I recently came up with a more simplified version. This works for positive and negative numbers.

=DATE(YEAR([Original Date]@row ) + (FLOOR(MONTH([Original Date]@row ) + ([# of Months]@row - 1), 12) / 12), IF(MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12) = 0, 12, MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12)) + IF(IF(MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12) = 0, 12, MOD(MONTH([Original Date]@row ) + [# of Months]@row , 12)) < 0, 12, 0), DAY([Original Date]@row ))

Tanya B

@Paul Newcome Thank you, I will have to give it a try

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.