Problems report filter and logic formula not recognizing today and yesterday in date column

Gordon

I'm trying to get my report to filter based on rows where the date column "Last Response Received" is not today or yesterday:

Last Response Received is a date column that has another formula: MAX(COLLECT(External sheet reference)...) which returns the most recent date (from a user's input of form data into a separate sheet) that is sourced from the system created on column.

The problem is that the report is showing rows that have yesterday's and today's dates in it, in addition to the rows that actually meet the criteria.

I can replicate the same incorrect results in a helper column that has =IF(OR([Last Response Received]@row<>TODAY(),[Last Response Received]@row<>TODAY(-1)),"LATE","NOT LATE")

Using the above formula, it returns "LATE" for rows where Last Response Received is yesterday's date.

What am I doing wrong? Is this somehow related to the recurring issue that Smartsheet has with dates and timezones? (Can they hurry up and fix this please?)

Thanks in advance for your help!

Gordon

I figured out why my report filter and formula was not working. When I converted the system column Modified date back into a date value, it only captures a two-digit year. Smartsheet then interprets a two-digit year input as 19xx instead of 20xx, thus the Last Response Received column was being read as 10/1/1920 instead of 10/1/2020. 😡

Maybe Smartsheet should consider updating it's date logic default to get into the 20th century... 🧐

Gordon

I should add that the IF formula in my helper column returns "LATE" for every row, regardless of what the date in Last Response Received is. I've already tried inserting DATEONLY as a wrapper function in front of the MAX(COLLECT... function in Last Response Received, but there's no effect.

Gordon

I figured out why my report filter and formula was not working. When I converted the system column Modified date back into a date value, it only captures a two-digit year. Smartsheet then interprets a two-digit year input as 19xx instead of 20xx, thus the Last Response Received column was being read as 10/1/1920 instead of 10/1/2020. 😡

Maybe Smartsheet should consider updating it's date logic default to get into the 20th century... 🧐