Issues with TODAY() and daily reporting

WerkinGrg22

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?

Paul Newcome

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 + ""))

Paul Newcome

Happy to help. 👍️

Paul Newcome

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 + ""))

WerkinGrg22

Oh dip. That took you know time at all. Thank you. This is amazingly helpful.

Paul Newcome

Happy to help. 👍️