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

gcoffey

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

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