Formula Counting Created Date as Following Day When Recorded After 5:30 PM PDT

Katy H

When creating a metric sheet that evaluates entries in another sheet, I am attempting to write a COUNTIF formula that counts all entries for that day, but any entries recorded after 5:30 PM PDT are counted towards the following day. I created a helper column that converts the system column into just a date, and that fixes the issue. I am curious if anyone knows the reason why this happens? Tried searching for the issue, but was unable to diagnose on my own.

Thank you!

Kelly Moore

Hey @Katy H

The problem is known problem caused by the difference in the sheet owner's midnight and the midnight of how the system dates (Created and Modified) are stored in smartsheet. Although the dates appear in your timezone, the data is stored as differently. There are various posts in the community for fixing this but since you are only interested in days, the easiest fix is with a helper Date column and using Record a Date automation. Record a Date will record the date in your actual timezone. Use this date field in your formula instead of Created.

Step 1 - Create a helper Date column. You can name it anything. You can keep this column hidden and shoved to the far right if desired. In my example below I called it Recorded Date.

Step 2 - Create a Record a Date automation.

You could create the automation from a template if you prefer. This one is so simple I created it from scratch.

Trigger = When rows are added or changed. Don't forget, as a good practice, to name your automation. Choose the Created column as your column that is changing.

Choose Record Date as the Action

Select the helper date column* you created. *Your column names will be in your list.

Will this work for you?

Kelly