6 Month Anniversary Formula

ksmith870

Hello Everyone,

I am an excel wiz but I realize formula writing is totally different within smartsheet. I need help projecting 6 months in advance date. I used the following formula. =DATE(([Start Date]6) + 1),(([Start Date]6) - 6),([Start Date]6)). Any help would be great!

Paul Newcome

Try this:

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

Paul Newcome

Try this:

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

ksmith870

I appreciate your help Paul! I copied and pasted the formula but now get #invalid data type. I was getting this yesterday when attempting other formulas recommended within Community.

Paul Newcome

Is the [Start Date] column set as a date type column?

ksmith870

Thank you for helping me! I made it a Date column

Paul Newcome

Is it working now that you made the reference column a date type?

ksmith870

Thank you for helping me! I made it a Date column. It also looks like I have to expand the formula even more. If One of the columns contain text "W-2" or "Non-compete", instead of a date, I want it to return "N/A"

ksmith870

=IF(([3rd Party Hire]CONTAINS("W-2"), "N/A", (IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 5, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 7, DAY([Start Date]@row))))

ksmith870

Actually, I got it!

=IF(OR([3rd Party Hire]@row = "W-2", [Start Date]@row = "TBD", [3rd Party Hire]@row = "Non-compete"), "N/A", (IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 5, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) - 7, DAY([Start Date]@row)))))

Paul Newcome

Happy to help. 👍️

Glad you were able to get it sorted.