Date Formula Error: Invalid Value

Mary Lee

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?

Paul Newcome

It looks like some things may have gotten shifted when you copied it over.

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

Mathieu PERSICO

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

Paul Newcome

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

Mary Lee

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 +/-?

Paul Newcome

@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.

Mary Lee

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

I received Unparseable.

Paul Newcome

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

Mary Lee

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.

Paul Newcome

It looks like some things may have gotten shifted when you copied it over.

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

Mary Lee

This works!!! Thank you so much!

Paul Newcome

Happy to help. 👍️

lgonzales

@Paul Newcome saves my day again 😅