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

@Pestomania Give this a try:

=IFERROR(DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) + 1, 1), DATE(YEAR([Approved Date]@row) + 2, 1, 1)) - 1

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.

Pestomania

Hi Paul,

Are you able to help me identify how to add 12 months or just 1 year and need the last day of that month as well?

I tried adding just 1 year (and 1 month) but keeps breaking it. If the date is in December, it creates an error.

=DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) + 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) - 11, 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) - 11, 1)) - 1) = 12, 1, 0))

I am pretty sure I am just all over the place wrong.

Paul Newcome

@Pestomania Give this a try:

=IFERROR(DATE(YEAR([Approved Date]@row) + 1, MONTH([Approved Date]@row) + 1, 1), DATE(YEAR([Approved Date]@row) + 2, 1, 1)) - 1

Pestomania

Talk about making that more difficult than it needed to be 😂

I ended up creating two helper columns (Review Date for 6 months and for 12 months) to avoid having to troubleshoot a long formula if I ever need it individually later.

Thanks !

Paul Newcome

Happy to help. 👍️