How can I use COUNTIF to find a total after a certain time

Brad Mulder

I am looking to count the amount of calls received after business hours from an exported sheet. In the below example, we received 6 calls after 5:00 PM, is there a way to calculate this?

07/15/20 4:54 PM 4079481564 Answered Linkcall 0 30 3

07/15/20 4:54 PM 6782966366 Answered Linkcall 167 607 414

07/15/20 4:56 PM 3343321518 Answered 10

07/15/20 4:57 PM 7706826006 Answered 13

07/15/20 4:58 PM 4079481564 Answered Linkcall 27 63 10

07/15/20 5:00 PM 4079481564 Answered 4

07/15/20 5:00 PM 4079481564 Answered Linkcall Abandoned 0 51 253

07/15/20 5:08 PM 5703506700 Answered 7

07/15/20 5:18 PM 5612552726 Answered VoiceMail 49

07/15/20 5:22 PM 3057371719 Answered 19

07/15/20 5:39 PM 4073740179 Answered VoiceMail 32

07/15/20 5:40 PM 4073740179 Answered Linkcall Abandoned 43

07/15/20 10:14 PM 3529781734 Answered VoiceMail 33

Paul Newcome

You are going to want to insert a "helper" column and use a formula such as the below to pull the time into a usable format (under the assumption that "07/15/20 4:54 PM" is all in the same cell of a system generated column).

=VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row) - (FIND(" ", [System Generated Column]@row) + 1))) + IF(FIND("P", [System Generated Column]@row) > 0, IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row) - (FIND(" ", [System Generated Column]@row) + 1))) <> 12, 12), IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row) - (FIND(" ", [System Generated Column]@row) + 1))) = 12, -12))

This will pull the hour and convert it into a 24 hour format.

Then you can use a COUNTIFS on this helper column to count how many are greater than or equal to 17 (5pm).

=COUNTIFS([Helper Column]:[Helper Column], @cell >= 17)

I am going to do some testing to see if we can do it without the helper column and will revisit if I find something.

Paul Newcome

You are going to want to insert a "helper" column and use a formula such as the below to pull the time into a usable format (under the assumption that "07/15/20 4:54 PM" is all in the same cell of a system generated column).

=VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row) - (FIND(" ", [System Generated Column]@row) + 1))) + IF(FIND("P", [System Generated Column]@row) > 0, IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row) - (FIND(" ", [System Generated Column]@row) + 1))) <> 12, 12), IF(VALUE(MID([System Generated Column]@row, FIND(" ", [System Generated Column]@row) + 1, FIND(":", [System Generated Column]@row) - (FIND(" ", [System Generated Column]@row) + 1))) = 12, -12))

This will pull the hour and convert it into a 24 hour format.

Then you can use a COUNTIFS on this helper column to count how many are greater than or equal to 17 (5pm).

=COUNTIFS([Helper Column]:[Helper Column], @cell >= 17)

I am going to do some testing to see if we can do it without the helper column and will revisit if I find something.

Brad Mulder

Thank you Paul! There are two columns, 1st column has the date, the 2nd has the time in this format (4:08 PM). How does that change the formula above?

Thanks again,

Brad!

Paul Newcome

I couldn't quite make it work without the helper column unfortunately.

Brad Mulder

I got #UPPARSEABLE error. On the sheet it's the 2nd column with a Column heading of "Inbound Call Time" the cell data is "5:08 PM".

I used the following:

=VALUE(MID([Inbound Call Time]@row, FIND(" ", [Inbound Call Time]@row) + 1, FIND(":", [Inbound Call Time]@row) - (FIND(" ", [Inbound Call Time]@row) + 1))) + IF(FIND("P", [[Inbound Call Time]@row) > 0, IF(VALUE(MID([Inbound Call Time]@row, FIND(" ", [Inbound Call Time]@row) + 1, FIND(":", [Inbound Call Time]@row) - (FIND(" ", [Inbound Call Time]@row) + 1))) <> 12, 12), IF(VALUE(MID([Inbound Call Time]@row, FIND(" ", [Inbound Call Time]@row) + 1, FIND(":", [Inbound Call Time]@row) - (FIND(" ", [Inbound Call Time]@row) + 1))) = 12, -12))

Paul Newcome

It changes the formula in the helper column like so...

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(FIND("P", [Time Column]@row) > 0, IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Brad Mulder

Worked perfectly, THANK YOU!

Paul Newcome

Happy to help. 👍️