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?

Find more posts tagged with

Formulas

Reports

Time Calculation

timekeeping

time zone

Accepted answers

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. 👍️

All comments

Paul Newcome

This is a known issue. To get the correct date you will need to put this into a date type column:

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.