Date 6 months out (to the day)

cpo1

Hello colleagues and guests. I would like to learn a forumula to retrieve a date that is exactly six months later than a known date; i.e. Jan 1 - July 1, Feb 4 - Aug 4. Presently, I'm using 182 days, which is close, but not exact. I'm also new at SS, so I am just now learning syntex, etc., regarding functions and formulas. Help is appreciated.

Paul Newcome

Yes. It is very possible. The most basic way of doing it is using the DATE function in combination with the YEAR, MONTH, and DAY functions and adding 6 to the month number.

 

=DATE(YEAR([Known Date Column]@row), MONTH([Known Date Column]@row) + 6, DAY([Known Date Column]@row))

.

However... This will only work for January through June. Any number greater than 12 in the month portion of the DATE function will throw an error.

The formula for months that will extend beyond 12 add 1 to the year and then subtract 6 from the months.

=DATE(YEAR([Known Date Column]@row) + 1, MONTH([Known Date Column]@row) - 6, DAY([Known Date Column]@row))

.

There are two ways to combine these. One would be to use an IF statement, and the other would be to use an IFERROR statement. They both would work almost identically in context with only slight differences in syntax.

.

For the IF statement, we will say that: if the month of the known date is less than or equal to 6, run the first formula, else run the second formula.

 

=IF(MONTH([Known Date Column]@row) <=6, DATE(YEAR([Known Date Column]@row), MONTH([Known Date Column]@row) + 6, DAY([Known Date Column]@row)), DATE(YEAR([Known Date Column]@row) + 1, MONTH([Known Date Column]@row) - 6, DAY([Known Date Column]@row)))

.

The IFERROR is very similar. Basically it says: do this, but if there is an error then do that.

 

=IFERROR(DATE(YEAR([Known Date Column]@row), MONTH([Known Date Column]@row) + 6, DAY([Known Date Column]@row)), DATE(YEAR([Known Date Column]@row) + 1, MONTH([Known Date Column]@row) - 6, DAY([Known Date Column]@row)))

Paul Newcome

Also... To help with building formulas and learning the syntax, there are quite a few resources you can use. Obviously you have found the Community. I personally have learned A LOT from here.

 

There is also a template in the Solutions Center named "Smartsheet Formula Examples" (see attachment for screenshot of what it looks like in the Solution Center). You can download this template that contains all of the different functions along with their syntax and interactive examples of their use. If you happen to accidentally mess something up in the sheet, you can just delete it and re-download the template.

 

Another that is extremely helpful is this web page HERE. It is a listing of all of the different error messages you can receive, what they mean, causes, and some basic troubleshooting.

cpo1

Thanks for the help Paul. I appreciate it. I had gotten to the part where the year didn't like a change, so your explanation is a great help. I'll work through it soon. I had found and opened "Smartsheet Forumula Examples" which are a big help and I used the year,month,day (date function). It worked fine until a change of year was needed as you are quite aware. Also, thanks for the help references. I'll be perusing those soon as well.  

Paul Newcome

Happy to help! 

 

Let me know how it works out for you. If it doesn't I'd be happy to continue working on this with you.

Daniel Fitzpatrick

Hello, I'm trying to add 5 months to the Executive Summary for period EOM column. The formula I am using works for several months, but I get an error for the other months. Here is the formula that I am using:

=IFERROR(DATE(YEAR([Executive Summary for Period EOM]@row), MONTH([Executive Summary for Period EOM]@row) + 5, DAY([Executive Summary for Period EOM]@row)), DATE(YEAR([Executive Summary for Period EOM]@row) + 1, MONTH([Executive Summary for Period EOM]@row) - 5, DAY()))

Kelly Moore

Hi Daniel

It looks like the last Day term in your equation is the problem. It doesn't point to a cell. Try adding your column name@row, [Executive Summary for Period EOM]@row, to the Day parentheses.

Kelly

Daniel Fitzpatrick

Kelly,

Thanks very much! That was the fix:)

EimearC

@Paul Newcome Hi Paul, I hope you don't mind me asking you a question on an old thread? I am using the below formula that you posted which is great for adding 10 months to a date but I can't seem to get the IFERROR part working. Can you please take a look to see where I'm going wrong?

"=IFERROR(DATE(YEAR([Received Price List Date]@row), MONTH([Received Price List Date]@row) + 10, DAY([Received Price List Date]@row)), DATE(YEAR([Received Price List Date]@row) + 1, MONTH([Received Price List Date]@row) - 2, DAY([Received Price List Date]@row)))" is the formula working fine but getting an "Invalid Data Type" error when there is no data in Received Price List Date. I was hoping to change the formula to the below to account for this:

"=IFERROR(DATE(YEAR([Received Price List Date]@row), MONTH([Received Price List Date]@row) + 10, DAY([Received Price List Date]@row)), DATE(YEAR([Received Price List Date]@row) + 1, MONTH([Received Price List Date]@row) - 2, DAY([Received Price List Date]@row)), "")"

but now I receive an error for "Incorrect Argument Set". Can you advise where I should be putting the ,"" for the value iferror please?

Paul Newcome

@EimearC If you want to output a blank when there is no date, you will need to use a second IFERROR.

=IFERROR(IFERROR(DATE(YEAR([Received Price List Date]@row), MONTH([Received Price List Date]@row) + 10, DAY([Received Price List Date]@row)), DATE(YEAR([Received Price List Date]@row) + 1, MONTH([Received Price List Date]@row) - 2, DAY([Received Price List Date]@row))), "")

EimearC

@Paul Newcome You're a genius! I didn't realise this could be done, thank you so much!