Can you pull between two times when using the countifs function?

gcoffey ✭✭
edited 08/06/23 in Formulas and Functions

When using the Countifs function, can you reference the created column (which includes both the date and the time) and pull based on submissions between certain times.

EX: =countifs({Form Date}, TODAY() - 1, {Form Date}, "04:30pm:11:59pm")



  • SteyJ
    SteyJ ✭✭✭✭✭
    edited 08/06/23

    Short answer, yes you can.

    You will have to separate the created column into several helper columns, and then include the helper columns in a countif to check for PM, and then use the converted value of the time to use greater than or less than equations

    What this formula does is it checks for AM or PM, since you only have 430PM to 1159PM there is no need for values in the AM range,

    The next countif range is this, which removes the colon and converts to a value:

    =VALUE(REPLACE([TIME with COLON]@row, 2, 1, ""))

    The final countif range is just making sure that the previous countif function which checks for whether the day is today - 1

    note: I don't recommend using this formula as it isn't tested -- this is only to show you the capabilities of smartsheet and I don't recommend using this function because it will lag your sheet if the size is increased


    Jacob Stey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!