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?

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

Many thanks

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • Anthony M
    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?

    Apologies if this is answered already in the threads

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!