Add 6 months and end of month

Pestomania

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.

Paul Newcome

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

Pestomania

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

Paul Newcome

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

Pestomania

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.