# 6 Month Anniversary Formula

Options

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!

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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"

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Glad you were able to get it sorted.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!