COUNTIFS WITH TODAY ALWAYS 7 TOO HIGH

Options
Ika619
Ika619 ✭✭
edited 02/16/22 in Formulas and Functions

There is only 1 entry for today 02-16-22, as you can see. (Yes, Picked is an auto-generated date/time...it has to be.) The summary field answer should be 1, not 8.

I've tried adding an equals sign =Today(). I've tried adding @cell. I've tried adding @row. No matter what I do, the count is always 7 too many.

Why? How to resolve?


Thanks.

Answers

  • Tatiana Kardosova
    Options

    Hi Ika.

    Please try following formula:

    =COUNTIFS([DRIVER NAME]:[DRIVER NAME], "BAKER, JIM", Picked:Picked, TODAY())

    Tatiana

  • Ika619
    Ika619 ✭✭
    Options

    Thanks. I tried that. Unfortunately, it is still incorrect. See attached.


    There is still only 1 record with today's date, 2-16-22 for Jim Baker. Yet the answer is returning 8.

    If I double check by applying a filter, that shows only 1 entry.

    But remains incorrect in the formula in sheet summary. 🤔


    This is a big problem for me!! Helllp!!

  • Tatiana Kardosova
    Options

    Hi.

    Strange. Lets try this workaround:

    date_picket to be set up as date

    today to be set up as date


  • Ika619
    Ika619 ✭✭
    Options

    Tatiana, First of all, thank you for your creative solution to my problem.

    I created the three columns, entered the formulas, copied/pasted them throughout the entire column.

    And, guess what? The countifs formula still does not work.

    But, I discovered something... look...in the yellowed rows, the date_picked SOMETIMES doesn't copy over correctly. It's supposed to be identical to Picked. ?!?!

    The formula is =DATEONLY(Picked@row) !! My sheet has approximately 800 rows all with different driver names. And, guess what? This error is only occurring with BAKER, JIM.

    Again, stumped. What is wrong with BAKER, JIM's records?

    Thank you!


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Ika619

    I hope you're well and safe!

    This sounds like a Timezone issue.

    From Genevieve

    System Date columns store date stamps as UTC, or GMT (although it can display the value in your local time).

    This means that a formula looking at that cell will bring through the date value based on GMT, which is likely why you're seeing inconsistencies.

    However, the Record Date Automation is based on local time! You could select a change in the Created column as your trigger, which only happens when the row is created. I would suggest using this workflow to populate a different date column, then reference this in your Start date column... or use it instead of your Start date column? See: Set the Current Date with Record a Date Action

    Does that help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!