Date-based formulas now broken across 100+ sheets

Ian Schumann

As a result of the latest release and supposed "formula enhancements", we now have more than 100 client-facing project sheets whose "status" column is entirely broken. Previously our status column looked at the task Start Date, End Date, and Target Date, and intelligently spit out a text value such as Due Soon, In Progress, Threatened, and so on. After the release, 100% of those cells are now broken with #INVALID OPERATION

 

Here's an example cell from one of our status columns:

=IF(AND(ISDATE([Target Date]3), [Target Date]3 - Start3 < -1), "Threatened", IF(Done3 <> 1, IF(Start3 - TODAY() > 1, IF(TODAY() - Start3 > -4, "Starting Soon", "Scheduled"), IF(End3 - TODAY() > 0, IF(TODAY() - End3 > -4, "Due Soon", "In Progress"), "Overdue")), "Completed"))

 

We've been doing our own troubleshooting trying to figure out what exactly has become "Invalid" but have been unsuccessful so far, so we've had to resort to manually typing in the status of these for the time being.

 

This is absolutely unacceptable. We need assistance on this, or an explanation for a fix we can make ourselves, or a proper patch on the release, ASAP.

Kennedy Stomps

Hi Ian-- In our latest release, we implemented a new formula engine that has new formula functions, processes formulas faster, and provides clearer error messages. Formulas that should have produced an error in the past, but didn't due to a lack of precision, may now be showing errors. Try wrapping your formula within an IFERROR function to resolve this.

 

For example:

 

Original Formula: =WORKDAY([Start Date]1, 5)

New Formula: =​IFERROR(WORKDAY([Start Date]1, 5), 0)

 

Basically, if the Start Date is empty it would have previously displayed a 0, now it displays an error as it can't find the WORKDAY from an empty cell. By placing it in an IFERROR, you can force it to display a 0 if this is what you choose. 

 

I understand that you need to implement this across 100+ sheets and that this process will be a significant investment of time and energy for you. I completely understand the frustration and am going to pass this feedback on to our Development and Product teams. 

Ian Schumann

Thanks for the quick reply Kennedy.

 

This was initially confusing to us, because we didn't see any obvious cases where previously our formula should have produced an error of any kind. But on closer inspection, I think we've found a culprit. Will do some work rebuilding the formula, and will update here if we need further assistance.

 

Thanks and have a great day!

Ian Schumann

In case this helps anybody else ...

 

As was implied by Kennedy and as was mentioned by other users, specifically our issue was due to trying to evaluate DATE cells that happened to be empty in some cases. In previous versions it looks like SmartSheet would just ignore the arithmetic in that case, but as of the latest release it now throws an error.

 

Here's the correction we had to make ...

Originally our status column had something like this going (pseudo code) ...

• IF the Target Date cell has a date in it AND if that date is before the projected Start Date for that task, then mark the row as "Threatened" (and if not, do a bunch of other stuff)

 

This broke because it resulted in (sometimes) trying to run the arithmetic comparing to the Start Date even when the Target Date cell was empty. As of the release, that broke, so we had to change it to:

IF the Target Date cell has a date in it, THEN deteremine IF that date is before the projected Start Date -- if so, mark as "Threatened"; if not, do a bunch of other stuff

 

We appear to have solved the new breakage by nesting the date evaluation behind the first IF, in other words. Looks like we'll be fine from here.