# Count of entries in the last 24 hours

Options

Hello,

I've got a sheet to record our queries as they come in and I'm looking for a formula to count the number of entries received in the last 24 hours as a running total I can monitor. At the minute I have set it up to count the queries received yesterday as per formula below but I'd be glad of some help how I can amend this if possible?

Many thanks

Tags:

• ✭✭✭✭✭✭
Options

@Anthony M This can get really complicated really quickly! One of the problems with your ask is that a formula may be able to know what date to use, either by using the TODAY() function or referencing a Date column, but it can't know what time or hour it currently is unless that gets entered somewhere manually.

If you haven't read through this thread, I would suggest starting with it:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

We might be able to get close to on the hour with some Change Cell automations. Basically you would set one up for each hour to run every day at the appropriate time and record a number (1 - 24) based on the hour that it is running. Call the column you are updating with the change cells "current hour".

Then you would use one of the solutions in the Time Calcs thread to strip the hour and convert it into a 24 hour time. Call this one "Submitted Hour".

Next a text/number column with something along the lines of:

=IF(OR(AND([Date Received]@row = WORKDAY(TODAY(), -1), [Submitted Hour]@row>= [Current Hour]@row), [Date Received]@row = TODAY(), [Submitted Hour]@row<= [Current Hour]@row)), 1, 0)

Then you can just use a SUM on that last column.

• Options

Many thanks @Jeff Reisman and @Paul Newcome for those replies,

Sorry I'm a bit new to this and trying to figure it out. I do have an auto fill 'created date' column which already has day and time so I hoped a formula could be figured from that? But will I need to add additional columns as well?