# Date formulas

Options

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

• ✭✭✭✭✭✭
edited 05/22/23
Options

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.

Jason Tarpinian - Sevan Technology

Smartsheet Aligned Partner

• Options

Hi Jason

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

• ✭✭✭✭✭✭
Options

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

• Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!