Issues with TODAY() and daily reporting
Hi there. In part of my duties, I run a report that shows activities entered on a form from yesterday.
We do achieve "yesterday" by using the following formula:
=IF(DATEONLY(Created@row) = DATEONLY(TODAY()) - 1, "Yesterday", "Not Yesterday")
In using this formula we have noticed some alarming discrepancies. Reports with "Created" timestamps after 4:00 pm are treated differently in terms of their date. Namely, they roll over to the next day. Here is a screenshot to give you a better idea as to what I'm working with:
I have confirmed my timezone settings to be correct (I am PST).
As a reference, the "time stamp" column is a formula to remove the date segment from created. The date returns on every record reported after 4:00 pm. This is also confusing. The formula for the "Time-Stamp" column is:
=SUBSTITUTE(Created@row, DATEONLY(Created@row), "")
Are there known issues around time stamps issues? Am I secretly 8 hours ahead? Does the smartsheet staff have any insight here or has anyone else experienced issues like this?
Best Answers
-
This is a known issue. To get the correct date you will need to put this into a date type column:
=DATEONLY(Created@row) - IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1)))>= 4, MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1)) <> "12"), 1, 0)
To get the time stamp:
=RIGHT(Created@row + "", LEN(Created@row + "") - FIND(" ", Created@row + ""))
-
Happy to help. 👍️
Answers
-
This is a known issue. To get the correct date you will need to put this into a date type column:
=DATEONLY(Created@row) - IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1)))>= 4, MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1)) <> "12"), 1, 0)
To get the time stamp:
=RIGHT(Created@row + "", LEN(Created@row + "") - FIND(" ", Created@row + ""))
-
Oh dip. That took you know time at all. Thank you. This is amazingly helpful.
-
Happy to help. 👍️
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
- 146 Just for fun
- 63 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!