Count of entries in the last 24 hours

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?

=COUNTIFS([Date Received]:[Date Received], WORKDAY(TODAY(), -1))

Many thanks

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@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:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Paul Newcome

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.

Anthony M

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?

Apologies if this is answered already in the threads

Paul Newcome

You will still need the [Submitted Hour] and [Current Hour] columns as well as the automations described above. There should be a formula in the time calcs thread that will help you strip the hour from the created date column.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.