Countif on a date field seems to be rounding to the next date when it is after hours?
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!
Answers
-
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:
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
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?
-
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:
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!