Adding months automatically

Hi all

I am trying to automatically add a 6 month review date to a start date, it is working fine until a start date is past June, to which I get a #invalid value error. The formula I am using is:

=DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))

So 1/6/19 becomes 1/12/19 but if I try with 1/7/19 I get #invalid value instead of 1/1/20.

Can anyone help????

Many thanks

Issy

Find more posts tagged with

Construction

Formulas

Project Management

Accepted answers

Paul Newcome

@Andrew Speer I have actually developed a slightly different approach. It is very similar but with a few tweaks to make it easier to scale and adjust.

=IFERROR(DATE(YEAR(A2), MONTH(A2) + 6, DAY(A2)), DATE(YEAR(A2) + 1, MONTH(A2) - 6, DAY(A2)))

Paul Newcome

@azerty64 18 months is basically 1 year and 6 months. You should be able to use the solution I posted on 23 June 2020 when I tagged Andrew but add 1 to each of the YEAR functions.

All comments

Mike Wilday

Try replacing A2 with [Title of Header for that column]2

Smartsheet refers to columns by their header title.

Paul Newcome

That is because the DATE function is trying to produce the date of 1/13/19 with the 13 coming from the month 7 plus 6. You will need to use an IFERROR statement. Something along the lines of...

=IFERROR(DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)), =DATE(YEAR(A2) + 1,(MONTH(A2)+6) - 12,DAY(A2)))

Basically this says to run your normal formula. If there is an error (caused by incorrect month numbers, it will run the other formula. The other formula basically subtracts 12 from the product of month number plus six which in your above example turns 13 into 1. It then adds those 12 months to the year which kicks it into next year.

There are a few other ways to accomplish this, but this was the first one I thought of. Hope this helps.

Andrew Speer

Hi Paul, thanks for your advice, I'm trying to achieve the same as IssyA.

Unfortunately I'm still coming up with error #INVALID OPERATION for months 7-12 based on this formula.

=IFERROR(DATE(YEAR(A2),MONTH(A2)+6,DAY(A2)), =DATE(YEAR(A2) + 1,(MONTH(A2)+6) - 12,DAY(A2)))

Is there a slight error in the formula somewhere, or is it just me?

Thanks in advance.

Paul Newcome

@Andrew Speer I have actually developed a slightly different approach. It is very similar but with a few tweaks to make it easier to scale and adjust.

=IFERROR(DATE(YEAR(A2), MONTH(A2) + 6, DAY(A2)), DATE(YEAR(A2) + 1, MONTH(A2) - 6, DAY(A2)))

MikeS

@Paul Newcome What if I wanted to add 24 months to a date?

Paul Newcome

@MikeS If you want to add 24 months to a date then you just need to add two years.

MikeS

Thanks @Paul Newcome. I guess it to was too late for me to keep working last night. That worked perfectly this morning.

azerty64

Hello and Sorry @Paul Newcome But : What if I wanted to add 18 months to a date?

Paul Newcome

@azerty64 18 months is basically 1 year and 6 months. You should be able to use the solution I posted on 23 June 2020 when I tagged Andrew but add 1 to each of the YEAR functions.

azerty64

https://community.smartsheet.com/discussion/comment/344721#Comment_344721

Perfect Thank you !

Paul Newcome

@azerty64 Happy to help. 👍️

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.