Problem: I want to calculate the number of days past the End Date or the number of days until the end date. If the End date is less than today, it would be "Overdue by " X days and if the End Date is greater than todays date, it would be "Days to Complete ". If the Status is Not Started or Completed, then leave the field blank.
The End date is a date column. A formula is used to populate the date in this column.
Formula created:
=IF(OR(Status@row = "Not Started", Status@row = "Completed"), "", IF([End Date]@row < TODAY(), "Overdue By " + (TODAY() - [End Date]@row ), IF([End Date]@row > TODAY(), ([End Date]@row - TODAY()) + " days to Complete", "")))
I keep getting #Invalid Operation Error. I've checked the operators and they are all valid based on what Smartsheet has outlined in documentation. The spelling and syntax appear to be correct.
Help is greatly appreciated! Thank you.
Screen shots are attached.
