Issues with TODAY formula

paugiber

I'm using this formula (=COUNTIFS({FINAL - Post Check Tracker Range 24}, "Outstanding", {Post Check) to count entries completed today from another sheet. The formula is pulling information from the automated "Created" column in the other sheet and it appears to be counting more than just todays entries. Any assistance with this would be greatly appreciated.

Kelly Moore

Hey @paugiber

It appears the formula above you are using became truncated in your post and at least one criteria is missing.

To count data based on today's Created date, your formula should be

=COUNTIFS({FINAL - Post Check Tracker Range 24}, "Outstanding", {Post Check Tracker Created}, TODAY())

Is this the formula you are using? If it is, does the discrepancy in count correlate to specific times in the Created timestamp?

Kelly

paugiber

Hi @Kelly Moore,

Apologies for the missing info. Yes, that is the correct formula and yes, that formula is pulling from the Auto-System Created column in the other sheet.

Thank you for the assistance!

Kelly Moore

Hey @paugiber

Please describe how the data is not what you expected (a screenshot helps).

Is the source sheet dormant for multiple days at a time- no automation, no saving, no opening the sheet, etc? If you think TODAY() is losing track of what day it is due to sheet inactivity, please see @Andrée Starå solution. I personally use this on almost all of my sheets.

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Kelly

paugiber

Hi @Kelly Moore,

Today for example, shows that we have 88 post checks when in reality we have 74. The formula seems to be counting 14 additional post checks from. Thoughts?

Thank you for the suggestion on the other post. I will check that out.

Kelly Moore

Hey

What timezone are you in? For example, I'm in US Central, which is GMT-6. You? I ask because there is a known timezone issue associated with the Created field. I wondered from the beginning if this was your issue - which I still don't know if it is. The Created date is stored as UTC-0, which means in my timezone I have discrepancies with the date when the data entry occurs between my 6pm and my midnight because midnight has already occurred in UTC0. (It actually is either 6pm or 7pm, seasonally, because daylight savings further complicates the issue). When I have data entry occurring in the night, I get around this problem by using the Record Date Automation. Record Date is always recorded in your timezone day. I trigger off of the Created column changing. The time stamp of the Created field is always correct, if you're parsing that anywhere. It's only the date, and only if the row was created in the window of time between midnight UTC0 and your midnight. Yes, it's complicated- but an easy workaround if that's the problem.

Check your created field and see if the extra days are occurring at the same time of day. I noticed it was around 5pm. The other check is to add a helper date field and add the Record Date automation. Compare counts between the two fields tomorrow.