Counting the Number of a certain job code working in each hour w/ 24hr

[hammerb23]

I am trying to set up a workbook to count the number of each job class that is working at a certain time, I am using this formula: =COUNTIFS(Data!$AA:$AA,Daily!$A3,Data!$J:$J,Daily!$C3,Data!$S:$S,"<="&Daily!E$1,Data!$V:$V,">="&Daily!E$2)
Data!$AA:$AA= jobcode
Daily!$a3= Jobcode I want to count
Data!$J:$J= Date
Daily!$C3= Day I want to Count
Data!$S:$S= time in, (formatted in 24hr)
Data!$V:$V= Time out (Formatted in 24hr)
Daily!E1-Daily!AB2 each hour period example E1= 00:00, E2= 00:59

it is counting fine for the hour periods between 05:00-22:59, but it is counting 0 between 23:00 and 04:59.

any help would be very much appreciated.

 

[James006]

Hi,

You could test following :

=mod(Daily!E1-Daily!AB2,1)

Hope this will help