#invalid value on formula

WinaHath

please help me; this formula is returning an "invalid value. The column is a Date

=IF([Date of Last Invoice]@row < DATE(YEAR(TODAY()) - 2, MONTH(TODAY()) + 6, DAY(TODAY())), "Inactive", "Active")

Nick Korna

Hi @WinaHath,

The MONTH portion of this will fail as the month portion of today + 6 is 15 - as there isn't a 15th month in the year this won't work.

Are you looking for the formula to show inactive accounts over 18 months old? If so then this should do the trick for you:

=IF([Date of Last Invoice]@row <= DATE(IF(MONTH(TODAY()) < 7, (YEAR([Date of Last Invoice]@row) - 2), (YEAR(TODAY()) - 1)), IF(MONTH(TODAY()) < 7, (MONTH(TODAY()) + 6), (MONTH(TODAY()) - 6)), DAY(TODAY())), "Inactive", "Active")

Sample data:

Hope this helps - if I've misunderstood something or you have any problems/questions then just post! 🙂

Alpha Chucky

The error you're experiencing likely stems from the fact that adding 6 to the MONTH(TODAY()) can result in an invalid date value. For instance, if today's month is July (7), adding 6 would result in 13, which isn't a valid month number.

Instead, you'd need to increment the year when the month exceeds 12 and reset the month appropriately. However, I don't think Smartsheet has a built-in function to handle month overflow directly. So, you'll need a more complex workaround:

Here's a formula that checks if the [Date of Last Invoice] is less than the same date 18 months ago (which is 1 year minus 6 months):

=IF([Date of Last Invoice]@row < IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()) - 2, MONTH(TODAY()) + 6, DAY(TODAY())), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) - 6, DAY(TODAY()))), "Inactive", "Active")

Here's a breakdown of the formula:

• If the current month is January through June (i.e., MONTH(TODAY()) is less than or equal to 6), it subtracts 2 years and adds 6 months.
• If the current month is July through December, it subtracts 1 year and 6 months.

This formula should ensure that the generated date is always valid and should avoid the "invalid value" error you were encountering.

Give it a go, and let me know if it works the way you want.

thanks