Search code examples
excel-formulacountifsumifs

How to create a statement to count total of date and time

I have data in two separate columns like this

two columns

But how can I count how many rows are occurring within say a window of 6 AM to 10 PM and outside a window of 10:01 PM to 5:59 AM?

The first column cells are formatted as General. The second column cells are custom h:mm:ss

Fuller View

Here I have a formula to count how many times "Mon" comes up in the left column

=COUNTIF($D:$D,"*"&$K5&"*")

And this is what I have when I try to combine the two to determine how many "Mon" entries were between 06:00 AM and 10:30 PM

=COUNTIFS($D:$D,"*"&$K5&"*",$E:$E,SUMPRODUCT((E2:E119>=M4)*(E2:E119<N4)))

but I get a fat 'ol zero

Yet if I run

=SUMPRODUCT((E2:E119>=M4)*(E2:E119<N4))

I get 116 as a return for the amount of times that a cell is between 06:00 AM and 10:30 PM

So BLUF - I want to count how many times a row has "Mon" and a corresponding time between 06:00 AM and 10:30 PM then I can replicate it for the rest of the days of the week.

New update-dtd 10/11/2022

Right now I know that there are 4 instances where Tuesday had after-hours calls: Instance one was at 5:59 AM on the 11th Instance two was at 5:57 AM on the 11th Instance three was at 10:39 PM on the 11th & Instance four was at 10:36 PM on the 11th

but with the formula of =COUNTIFS($D:$D,""&$K7&"",$E:$E,">="&$O$5,$E:$E,">"&$P$5) I am only accounting for 2 of those instances - the 10:39 PM and the 10:36 PM instance (found this out by testing/removing the values in the cells to see what changed the total). How would I capture the times that are between midnight and 05:59 AM, to include 5:59 AM? enter image description here

Solution

  • @ScottCraner solved this with =COUNTIFS($D:$D,""&$K5&"",$E:$E,">="&M4,$E:$E,"<"&N4)