Date formulas

Richard Stanton

Hi

I am using the below formula to add 6 months to a date. It has been operating perfectly whilst the result falls into the same year as the original date. Now that the result falls into the next year, the formula returns #INVALID VALUE. Can anyone assist, please?

=IF([SUNSET EXTENSION]@row = "1st 6 Month", DATE(YEAR([ORIGINAL SUNSET DATE]@row), MONTH([ORIGINAL SUNSET DATE]@row) + 6, DAY([ORIGINAL SUNSET DATE]@row)), IF([SUNSET EXTENSION]@row = "2nd 6 Month", DATE(YEAR([ORIGINAL SUNSET DATE]@row) + 1, MONTH([ORIGINAL SUNSET DATE]@row), DAY([ORIGINAL SUNSET DATE]@row)), [ORIGINAL SUNSET DATE]@row))

Jason Tarpinian

I'm not sure what you're using the "SUNSET EXTENSION" column for, but if it's a helper, you should be able to get rid of it with this formula:

=DATE(IF(MONTH([ORIGINAL SUNSET DATE]@row) >= 7, YEAR([ORIGINAL SUNSET DATE]@row) + 1, YEAR([ORIGINAL SUNSET DATE]@row)), IF(MONTH([ORIGINAL SUNSET DATE]@row) >= 7, MONTH([ORIGINAL SUNSET DATE]@row) - 6, MONTH([ORIGINAL SUNSET DATE]@row) + 6), DAY([ORIGINAL SUNSET DATE]@row))

This will adjust the year/month if it's past June to account for the changing of the year.

I just realized that this will be off if you have months whose days don't line up - like 8/31/23 to February wouldn't have a 31st... (using my formula as is, it returns 3/2/24) that part would be quite a bit trickier and can't think of right now.

Richard Stanton

Hi Jason

Thanks for trying to assist. Here is a snapshot of the sheet.

Debbie Sawyer

@Richard Stanton

This is the correct result for the data showing in the sheet. (from the example screenshot)

The Original Date is 6th July 2023, the Sunset Extension is showing "1st 6 Month", so the formula in Current Sunset Date is performing the 1st part of the IF function which says, If Sunset Extension = 1st 6 Month, then set the date to the Year from Original Date (2023) the Month from Original Date +6 and the Day to the Original Day from the Original Date (6).

This is returning an error because you can't "add 6" to month "7" and get a "valid" month in return.

Surely if the first date is July, then the Sunset Extension should be set to "2nd 6 Month"? If so, your errored value would become:

Year from Original Date +1 (2024) the Month from Original Date (7) and the Day to the Original Day from the Original Date (6).

It would then return 06/07/24 and not an error.

I think you just need to change "1st 6 Month" to "2nd 6 Month" in the Sunset Extension column!

Apologies if I have mis-understood your data.

Kind regards

Debbie

Richard Stanton

Thank you for the help. Below is the formula that I have used, and it works great.

=IF([SUNSET EXTENSION]@row = "1st 6 Month", DATE(YEAR([ORIGINAL SUNSET DATE]@row) + ROUNDDOWN((MONTH([ORIGINAL SUNSET DATE]@row) + [Months_1]#) / 12, 0) + IF(IF(MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_1]#, 12) = 0, 12, MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_1]#, 12)) = 12, -1), IF(MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_1]#, 12) = 0, 12, MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_1]#, 12)), DAY([ORIGINAL SUNSET DATE]@row)), IF([SUNSET EXTENSION]@row = "2nd 6 Month", DATE(YEAR([ORIGINAL SUNSET DATE]@row) + ROUNDDOWN((MONTH([ORIGINAL SUNSET DATE]@row) + [Months_2]#) / 12, 0) + IF(IF(MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_2]#, 12) = 0, 12, MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_2]#, 12)) = 12, -1), IF(MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_2]#, 12) = 0, 12, MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_2]#, 12)), DAY([ORIGINAL SUNSET DATE]@row)), IF([SUNSET EXTENSION]@row = "3rd 6 Month", DATE(YEAR([ORIGINAL SUNSET DATE]@row) + ROUNDDOWN((MONTH([ORIGINAL SUNSET DATE]@row) + [Months_3]#) / 12, 0) + IF(IF(MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_3]#, 12) = 0, 12, MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_3]#, 12)) = 12, -1), IF(MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_3]#, 12) = 0, 12, MOD(MONTH([ORIGINAL SUNSET DATE]@row) + [Months_3]#, 12)), DAY([ORIGINAL SUNSET DATE]@row)), [ORIGINAL SUNSET DATE]@row)))