COUNTIF with Todays date changes on full sheet load

Brenton Gaw

G'day

I'm having an issue when using =COUNTIF with a submitted date.

I'm trying to count the number of submissions to another sheet for; Today, the last 7 days, the last 30 days, and so on.

The calculation seems to be working, but the number changes very quickly after the page loads. For example, shows the count as 8, then switches to 1. (images attached)

After reading some other threads I am using a sheet summary for today's date in the hope that might solve the issue, it has not.

I am current using the formulars below

=COUNTIF({submit date}, [Todays date]#)

=COUNTIF({submit date}, >=[-7]#)

where [Todays date] and [-7] are sheet summary fields.

The issue has also happend using the below

=COUNTIF({submit date}, >=TODAY(-7))

This data is then being used in a dashboard which is often showing the number loaded initially which is not ideal.

Any help to solve the issue would be great

Paul Newcome

It is because the TODAY function does not update to today's date until the sheet has been activated. My suggestion to activate the sheet would be to enter a date type column and then set up a Record a Date automation to run daily at 12:00am and populate today's date in this new column. This will activate the sheet within a few minutes of the date changing and your counts should remain current even if you do not open the sheet. These updates should also pull through to your various dashboards and reports.

Brenton Gaw

Thanks for the answer Paul.

Would I then use this new date in the function or is its purpose only to activate the sheet and then still use TODAY for the function?

I ask this becasue the issue I am having now is that I am not in US Pacific time (GMT -7) so the dates are incorrect, depening on the time of day they occur.

I am in GMT +10. I have checked both the workspace owner and the sheet owner have their time zone set correctly to +10.

Paul Newcome

It is up to you which you use. If the date being populated is not always correct, then I suggest sticking with the TODAY reference and using this only for activating the sheet.

Brenton Gaw

This still gives me the same behaviour and the count is also out by -1.

Both sheets should be 'activating' but as I have said the count still jumps on full load and the count is down by 1 (at this stage) when using the below

=COUNTIF({submit date}, >=TODAY())

Paul Newcome

The automation should be on whichever sheet the TODAY function is on.