Problems report filter and logic formula not recognizing today and yesterday in date column
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!
Best Answer
-
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... 🧐
Answers
-
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.
-
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... 🧐
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!