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