4

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.

Comments

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

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.

In reply to by Paul Newcome

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.