Question

I've had formulas calculating the number of Salvage appointments that were added to our sheet on today's date and yesterday's date for over a week now with no issue. This morning I was notified that the numbers are incorrect. I am able to filter the grid and see that we did in fact have more than 8 salvage appointments created yesterday, and we haven't had 38 added today (We actually show 0 with a creation date of today). I've attached a link below to view the sheet data and formulas. It almost like it is going by a 24 hour calendar instead of date. Anyone else have this issue or think they know why my formulas aren't working?

To identify # of salvage appointments created today:

=COUNTIFS(Created:Created, TODAY(), [Load Contents]:[Load Contents], "Salvage")

To identify the # of salvage appointments created yesterday:

=COUNTIFS(Created:Created, TODAY(-1), [Load Contents]:[Load Contents], "Salvage")

Video showing master data vs the formula numbers:

https://app.screencast.com/BSurCPRdDG3SK

Jeff Reisman · Accepted Answer

@Ashley F

The system "Created" column, while it may appear to be a value in your local time zone, is in fact recorded in UTC. I am in US Eastern Daylight Time (UTC-4), so if I try to reference that column and say "count everything created yesterday," it's going to count everything created at 8pm or later two days ago, up until 7:59pm yesterday. My numbers are going to be off!

Set up a 'Record a Date' automation that fires off each time a row is created, and populate another date column. 'Record a Date' records the date according to the regional settings of the sheet owner. Then use that column in your formula instead of the system "Created" date.

Also, if you wouldn't mind, check out my idea for fixing this and give it an up vote!