# Date Formula Error: Invalid Value

Options

I'm trying to +/- months from a date, but I receive "invalid value" whenever there's a change in "year".

Example:

=DATE(YEAR(date12), MONTH(date12) + 6, DAY(date12))

If Date is 12/31/2020, then it creates an error because the new date needs to be in 2021 (6 months from 12/31/2020)

How can I fix this?

• edited 07/24/20
Options

Hi Mary,

Try this formula:

=IFERROR(DATE(YEAR(date@row), MONTH(date@row) + 6, DAY(date@row)), DATE(YEAR(date@row) + 1, MONTH(date@row) - 6, DAY(date@row)))

Hope this helps!

Have a nice day,

Mathieu | info@evolytion.com

evolytion.com

Mathieu | Workflow Consultant

info@evolytion.com

• ✭✭✭✭✭✭
Options

IS it a set number of months, or do the months vary?

• Options

The months vary. I put months in another column. How can I make the error formula more dynamic, so it'll always work no matter how many months +/-?

• ✭✭✭✭✭✭
Options

@Mary Lee Do a search in the community for "Edate". There are a handful of solutions that allow you to enter a number into a column and use that to adjust dates.

If you are unable to make one of those solutions work, feel free to let me know and we will try to figure something else out for you.

• Options

I tried this formula

=DATE(YEAR([Key date]10) + ROUNDDOWN((MONTH(YEAR([Key date]10) + [Months +/-]33) / 12, 0) + IF(IF(MOD(MONTH(YEAR([Key date]10) + [Months +/-]33, 12) = 0, 12, MOD(MONTH(YEAR([Key date]10) + [Months +/-]33)) = 12, -1), IF(MOD(MONTH(YEAR([Key date]10) + [Months +/-]33, 12) = 0, 12, MOD(MONTH(YEAR([Key date]10) + [Months +/-]33, 12)), DAY(YEAR([Key date]10))

• ✭✭✭✭✭✭
Options

Can you post a link to where you got it from along with a screenshot of your sheet to include the column names?

• Options

Grabbed the formula from here: https://community.smartsheet.com/discussion/66572/what-is-formula-edate-from-excel-in-smartsheet

So I'm updating the End Date based on "Months" column plus a static Key Date in one cell.

• Options

This works!!! Thank you so much!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

@Paul Newcome saves my day again 😅

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!