Can anybody explain this seemingly impossible formula result
We have this formula in the [Task Status] column of every one of our 82 production sheets (as a column formula). See the attached .txt file for a pretty printed version of the formula.
=IF([DTM?]@row,
IF(
OR(
ISBLANK([Start Date]@row),
ISBLANK([End Date]@row),
NOT(ISDATE([Start Date]@row)),
NOT(ISDATE([End Date]@row)),
IF([TM?]@row,
FALSE,
OR(
AND(
COUNTIFS(CHILDREN([DTM?]@row), @cell = 1, CHILDREN([Task Status]@row), @cell <> "Unscheduled") > 0,
COUNTIFS(CHILDREN([DTM?]@row), @cell = 1, CHILDREN([Task Status]@row), @cell <> "Unscheduled", CHILDREN(Complete@row), @cell < 1) = 0,
COUNTIFS(CHILDREN([DTM?]@row), @cell = 1, CHILDREN([Task Status]@row), @cell = "Unscheduled") > 0),
COUNTIFS(CHILDREN([DTM?]@row), @cell = 1, CHILDREN([Task Status]@row), @cell <> "Unscheduled") = 0))),
"Unscheduled",
IF(Complete@row = 1,
IF(IF(ISBLANK([Date Completed]@row), Today#, DATEONLY([Date Completed]@row)) < DATEONLY([End Date]@row),
"Completed Early",
IF(IF(ISBLANK([Date Completed]@row), Today#, DATEONLY([Date Completed]@row)) > DATEONLY([End Date]@row),
"Completed Late",
IF(IF(ISBLANK([Date Completed]@row), ([End Date]@row), DATEONLY([Date Completed]@row)) = Today#,
"Completed Today",
IF(IF(ISBLANK([Date Completed]@row), DATEONLY([End Date]@row), DATEONLY([Date Completed]@row)) > (Today# - 7),
"Completed Recently",
"Completed")))),
IF(Today# > DATEONLY([End Date]@row),
"Overdue",
IF(Complete@row = 0,
IF(Today# < DATEONLY([Start Date]@row),
"Not Due To Start",
"Not Yet Started"),
IF(Today# < DATEONLY([Start Date]@row),
"In Progress Early",
"In Progress"))))),
"")
The blank lines in the formula are unintentional and cropped up when I was pasting. Can't get rid of them. Plus the indentation in the code seems to get lost - see the attachment. Sorry.
From midday 14-May-2025 until late 16-May-2025, the value of [Task Status]390 in a particular sheet changed from "Completed Today" to "Overdue" and then back to "Completed Recently" and then back to "Overdue" and then back to "Completed Recently" (as shown by Cell History).
During this same period, the value of [Complete]390 remained at "1" (as shown by Cell History).
During this same period, the value of [End Date]390 remained at "14/5/25" (as shown by Cell History) - being 14-May-2025.
This situation cropped up when seeking to understand why Today# (which we set to the current date at 1am every morning in every sheet) wasn't being updated by our API.
Our regression test sheet of [Task Status] has been in place for many years. It deems this formula to meet our specification for the 11 status outcomes.
There has been no change to this formula for over two years.
I am completely stumped as to how this formula can EVER return "Overdue" when [Complete]@row is "1" (i.e. the task is 100% complete).
Any ideas, no matter how wild, will be appreciated.
Answers
-
Hi, @Rob Hagan.
Verify that the value of Complete@row is indeed the integer 1 instead of the string "1".
Or edit the expression to
VALUE(Complete@row) = 1. I've noticed that Smartsheet sometimes does weird things with expressions that use multiple functions.Looking over the formula, here's how it evaluates to "Overdue".
β’ [DTM?]@row = true or 1, AND;
β’ [End Date]@row exists and is a date value, AND;
β’ The value in Complete@row <> 1, or it is a string, AND;
β’ Today# > [End Date]@row.
-
Hi TVang,
I really appreciate the time that you have put in on my issue.
[Complete]@row is of type Text/Number and has values of <blank> or 0 through 1, so 0.5 is a task that is 50% complete and 0 or <blank> is a task that has not been commenced. Not sure of the effect of VALUE() over that content would do and I should find out (late at night here in Australia). [Complete]@row is being tested in many formulas to be = 0, > 0, < 1, etc. and is appearing to work correctly (over many, many years). Good thought though.
I've attached the three relevant screenshots. [Complete]390 is 1 from 14-May-2025 3:08PM onwards, whereas [Task Status]390 goes through multiple values from "Completed Today", through a couple of "Overdue"s mixed in with "Completed Recently"s. That's my core issue. How can an "Overdue" ever occur with the code as published while [Complete]390 remains at 1.
I've just now pushed this to Smartsheet as a support case, but I was trusting the Community more in the first instance to have ideas.
Thanks again,
Rob
-
Hi @Rob Hagan
This formula returns a task status like "Unscheduled", "Completed Early", "Overdue", etc., based on these key fields: DTM?, TM?, Start/End Dates, Date Completed, Complete, and Task Status.
Key Logic:
- If [DTM?] = 0, return blank
- If Start/End Date is missing or invalid, or child rows indicate incomplete/unplanned, return "Unscheduled"
- If Complete = 1, compare Date Completed to End Date:
- Before = "Completed Early"
- After = "Completed Late"
- Same as Today# = "Completed Today"
- Within last 7 days = "Completed Recently"
- Else = "Completed"
- If not complete:
- After End Date = "Overdue"
- Before Start Date = "Not Due To Start"
- Else = "In Progress"
Updated formula
=IF([DTM?]@row,
IF(
OR(
ISBLANK([Start Date]@row),
ISBLANK([End Date]@row),
NOT(ISDATE([Start Date]@row)),
NOT(ISDATE([End Date]@row)),
IF([TM?]@row, FALSE,
OR(
AND(
COUNTIFS(CHILDREN([DTM?]@row), 1, CHILDREN([Task Status]@row), <> "Unscheduled") > 0,
COUNTIFS(CHILDREN([DTM?]@row), 1, CHILDREN([Task Status]@row), <> "Unscheduled", CHILDREN(Complete@row), < 1) = 0,
COUNTIFS(CHILDREN([DTM?]@row), 1, CHILDREN([Task Status]@row), "Unscheduled") > 0
),
COUNTIFS(CHILDREN([DTM?]@row), 1, CHILDREN([Task Status]@row), <> "Unscheduled") = 0
)
)
),
"Unscheduled",
IF(Complete@row = 1,
IF(DATEONLY(IF(ISBLANK([Date Completed]@row), Today#, [Date Completed]@row)) < DATEONLY([End Date]@row), "Completed Early",
IF(DATEONLY(IF(ISBLANK([Date Completed]@row), Today#, [Date Completed]@row)) > DATEONLY([End Date]@row), "Completed Late",
IF(DATEONLY(IF(ISBLANK([Date Completed]@row), Today#, [Date Completed]@row)) = Today#, "Completed Today",
IF(DATEONLY(IF(ISBLANK([Date Completed]@row), Today#, [Date Completed]@row)) > (Today# - 7), "Completed Recently", "Completed")))),
IF(Today# > DATEONLY([End Date]@row), "Overdue",
IF(Today# < DATEONLY([Start Date]@row), "Not Due To Start", "In Progress"))
)
),
""
)
Time Zone Issue:
Smartsheetβs TODAY() function uses UTC time when evaluating formulas. This means if you're in Australia or Japan, values may shift a day earlier or later.
Reference:
Fix:
To correctly compare with your local date, convert the Today# field (stored as text) using this formula in a helper column:
TodayLocal#=DATE(2000 + VALUE(RIGHT(Today#, 2)), VALUE(LEFT(Today#, 2)), VALUE(MID(Today#, 4, 2)))
This creates TodayLocal# using your local date value.
To cope with the time zone issue, change the Today# to TodayLocal#.
-
Hi jmyzk_cloudsmart_jp,
Thank you for your input to my issue. I have raised a support case with Smartsheet and it is taking a long time for them to find a resolution. Because of the bug in Smartsheet's TODAY() as it applies in Australia, we have a Java program that runs every early morning and updates a field in the first base row of every production sheet with "today" obtained from the Java runtime. We know that this "today" is correct and is then used in various date computations with the sheet.
I could go to town on how ludicrous it is that a core function like TODAY() doesn't meet its specification when used in Australia. Maybe Smartsheet was not designed to operate outside of a limited number of timezones.
Apart the "today" issue that we fixed with a permanent workaround years ago, the challenge with this issue is that the formula returns a value that appears to be impossible but only some of the time. Every few weeks, it seems, the "impossible" values pop up and then the system returns to correct operation. This is most frustrating, and it reduces both staff trust in the system and management's trust in the system.
Fingers crossed that the senior technicians at Smartsheet can find a resolution, and soon.
Thanks again for your input.
Cheers,
Rob
Help Article Resources
Categories
Check out the Formula Handbook template!