# Add 6 months and end of month

Options
✭✭✭✭✭

Good morning,

I am trying to identify how to add 6 months to a column [Approved Date] but add 6 months and the last weekday of the month.

So if given 7/23/2024, the next review date would be 01/31/2025. Or in May 2025, it would return 5/30/2025. I tried a few formulas but just can't get it to work.

• ✭✭✭✭✭✭
Options

Give this a try:

=(IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) - IF(WEEKDAY(IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) = 1, 2, IF(WEEKDAY(IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) = 7, 1, 0))

• ✭✭✭✭✭
Options

I received an "Invalid Data Type" error, so I added an isblank portion into the formula (see below). Now I am receiving an "Invalid Operation" error.

=IF(ISBLANK([Approved Date]@row), " ", IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) - IF(WEEKDAY(IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) = 1, 2, IF(WEEKDAY(IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) = 7, 1, 0))

• ✭✭✭✭✭✭
Options

Try an IFERROR around the whole thing maybe.

=IFERROR((IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) - IF(WEEKDAY(IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) = 1, 2, IF(WEEKDAY(IFERROR(DATE(YEAR([Approved Date]@row), MONTH([Approved Date]@row) + 7, 1), DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) - 5, 1)) - 1) = 7, 1, 0)), "")

• ✭✭✭✭✭
Options

That worked.

I will say that it would be nice to have an option (similar to Excel) where you can step through a formula as it calculates and find the errors. I know why this one errored out but I am unable to do that with others that I create.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!