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

Options

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

  • Monique_Odom_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Options

    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

    Business Process Excellence Manager

    Smartsheet Leader & Community Champion

    Pronouns: She/Her (What’s this?)

    “Take chances, make mistakes, get messy!” – Ms. Frizzle

  • Justin Martine
    Options

    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_Comcast
    Monique_Odom_Comcast ✭✭✭✭✭✭
    Options

    @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:


    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

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!