COUNTIFS WITH TODAY ALWAYS 7 TOO HIGH

Ika619

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.

Tatiana Kardosova

Hi Ika.

Please try following formula:

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

Tatiana

Ika619

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

Hi.

Strange. Lets try this workaround:

date_picket to be set up as date

today to be set up as date

Ika619

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å

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!