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 Receivedis 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 whereLast Response Receivedis 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?)
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 theLast Response Receivedcolumn 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...
I should add that the IF formula in my helper column returns "LATE" for every row, regardless of what the date inLast Response Receivedis. I've already tried inserting DATEONLY as a wrapper function in front of the MAX(COLLECT... function inLast 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 theLast Response Receivedcolumn 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...
Try IF([payment voucher]@row=0,Sum([Parking Revenue Regular]@row:[Private boat parking revenue]@row),\"//www.santa-greenland.com/community/discussion/comment/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106883,"type":"question","name":"Needing some help with my current smartsheet project","excerpt":"So I'm coming across some issues with my workflows and functions with my current sheet, and I'm hoping somebody could help me out because I'm stumped. There are boxes I have set up on children rows that get checked manually to confirm a certain portion of the Main Task is complete. I'm currently in search of a way I can…","categoryID":322,"dateInserted":"2023-06-26T13:40:29+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T20:13:57+00:00","insertUserID":162756,"insertUser":{"userID":162756,"name":"SarahI","title":"Sarah","url":"https:\/\/community.smartsheet.com\/profile\/SarahI","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T19:19:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T20:54:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":41,"score":null,"hot":3375602066,"url":"https:\/\/community.smartsheet.com\/discussion\/106883\/needing-some-help-with-my-current-smartsheet-project","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106883\/needing-some-help-with-my-current-smartsheet-project","format":"Rich","lastPost":{"discussionID":106883,"commentID":382423,"name":"Re: Needing some help with my current smartsheet project","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382423#Comment_382423","dateInserted":"2023-06-26T20:13:57+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T20:54:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T15:51:35+00:00","dateAnswered":"2023-06-26T15:23:39+00:00","acceptedAnswers":[{"commentID":382304,"body":"
