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.

Adrian Mandile CHESS

Hi @Paul Newcome,

I used your formula for adding 6 months to a date and modified it for a given duration of months, and it worked fine if the Start Date wasn't the 31st and I added a number of months that made the Finish Date a month with less than 31 days. This is my formula:

Finish Date = IFERROR(IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Duration Months]@row, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + 1, MONTH([Start Date]@row) + [Duration Months]@row - 12, DAY([Start Date]@row))), "")

Works for: 31-Jan-2024 + 2 months = 31-Mar-2024
Doesn't work for 31-Jan-2024 + 3 months = 31-Apr-2024 (ERROR!)

This would also be an issue with any such calculation with a starting date the 31st that has a Finish Date in Apr, Jun, Sep, Nov (30-day months) or starting on 29th/30th/31st and has a finish date in Feb (only 28/29 days).

Any suggestions how we could make the day either roll forward to be 1st of th next month, or keep it to the last possible day of the finish month?