Countif on a date field seems to be rounding to the next date when it is after hours?

Justin Martine

I am using the Created Date auto-generated column for tracking purposes on a project. I'm trying to see how many entries happened on each day. I'm using =COUNTIF([Date Submitted],date reference)... very simple formula. It is showing I had an entry on a date where there clearly are zero entries. The only thing I can think of is there is one entry for the previous date at 10:25PM.

Does the formula round to the next day if its afterhours? I cannot find any settings to determine this or adjust this.

This seems to be giving me inaccurate data... any advice would be greatly appreciated!

Monique Odom-Stearn

Hello @Justin Martine,

My guess is that it's actually a time zone difference. The system-created columns (like the Created Date) are calculated on the back on using UTC, so this can affect any formulas you have pointing to these columns. My recommendation would be to have a helper column pulling just the date (and not time) from the Created Date column.

Please see this article for more information regarding time zone differences:

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

Justin Martine

Thanks Monique,

Do you know of a solution that would maintain the date it was submitted instead of showing the date the system calculates based on the timezone?

Monique Odom-Stearn

@Justin Martine,

Yes! You would still need a helper column, but instead of having a formula to pull the date from the Created Date field, you can set up an automation to record the date when a new row is added. It would look something like this: