Countif todays date and by someone

Kate Arrold

I want to count how many times someone completes a form today

I was able to count if yesterdays date with:

=COUNTIFS({Store}, $Name6, {Created}, TODAY() - 1)

but can't find a way to calculate todays entries

Mark Cronk

Hi @Kate Arrold ,

=COUNTIFS({Store}, $Name6, {Created}, TODAY()) should do it.

Mark

Mark Cronk

Try creating a helper date column [Date Created] and input the column formula =DATEONLY([created]@row)

I assume you're using a system column Created. If not change the formula accordingly. The Created column includes a time stamp. The DATEONLY function removes the time and retains just the date. Use the helper [Date Created] column for your COUNTIFS range.

Work?

Mark

Mark Cronk

Hi @Kate Arrold ,

=COUNTIFS({Store}, $Name6, {Created}, TODAY()) should do it.

Mark

Kate Arrold

Hi @Mark Cronk

I thought it was correct but my formula is still showing 0

I think it might not be refreshing and pulling the data through properly

Mark Cronk

Try creating a helper date column [Date Created] and input the column formula =DATEONLY([created]@row)

I assume you're using a system column Created. If not change the formula accordingly. The Created column includes a time stamp. The DATEONLY function removes the time and retains just the date. Use the helper [Date Created] column for your COUNTIFS range.

Work?

Mark

Kate Arrold

Hi @Mark Cronk

I am trying the above out now but would there be a reason when I use this function that it has a slightly different date

Mark Cronk

Interesting. I haven't encountered that. Time zone issue? Just a guess. DATEONLY() should just remove the time.

Here's an article with a solution. Known issue:

https://community.smartsheet.com/discussion/70719/why-is-dateonly-returning-the-wrong-date

Mark

Barbara Witt

@Mark Cronk allow me to tag onto this discussion. How could I capture "earlier than TODAY?

=COUNTIFS({DAX Ambient Inventory Actual Install Date}, ISDATE(@cell), {DAX Ambient Inventory Actual Install Date}, TODAY())

Where and how would I enter <?

Thanks so much for your assistance. Best regards, Barbara

Mark Cronk

Hi @Barbara Witt ,

=COUNTIFS({DAX Ambient Inventory Actual Install Date}, ISDATE(@cell), {DAX Ambient Inventory Actual Install Date}, <TODAY()) should do it.

Mark