Between --- in excel?

[neditheg]

HY!!!

I have a problem.

So I have : "Login" time and "Logout time" for each operators .. I need to know how many operators I have between --- 09:00 AM -- 10:00 AM ; 10:01--11:00 ... etc.

I've attached an example ( input sheet = what I have; output sheet = What I want).

Thanks!!!

[Bob Phillips]

F4: =IF(AND(F$2>=$C4,F$3<=$D4),"x","")

copy down and across

F9: =COUNTIF(F4:F8,"x")

copy across

[neditheg]

F4: =IF(AND(F$2>=$C4,F$3<=$D4),"x","")

copy down and across

F9: =COUNTIF(F4:F8,"x")

copy across

so ... with this formula I lose some "x" :)
-- in the 4th row i lose the "x" between 20:00 and 21:00 ( logout time =20:51:06) -- I can remediate this using
=IF(AND(F$2>=$C4,F$2<=$D4),"x","")
-- then in the 7th row I have no "x" -- because C7 (20:02:42) > S2 (20:00:00)

--another case ... 6th and 8th rows .. I lose the first "x" ... and I don't know why ...yet :)


still need an answer :P

thanks!

[neditheg]

so... maybe we can round the "login & logout time" using something like this :

=TIME(HOUR( A1),CEILING( MINUTE( A1), 60), 0)


but i need 15:07 :33 --> round at 15:00
and 15:57:09 --> round at 16:00

[Bob Phillips]

The formula was back to front, should be

=IF(AND(F$3>=$C4,F$2<=$D4),"x","")

but doesn't cater for overnighters still.

[neditheg]

=ROUND(C4 * 96; 0) / 96

for round up :) and I must find something for the overnights


thanks for ur help!!

[Bob Phillips]

This seems to cater for all

=IF($D4>$C4,IF(AND(F$3>=$C4,F$2<=$D4),"x",""),IF(OR(AND(1>=$C4,F$2<=$D4),AND(F$3>=$C4,0<=$D4)),"x",""))

[neditheg]

This seems to cater for all

=IF($D4>$C4,IF(AND(F$3>=$C4,F$2<=$D4),"x",""),IF(OR(AND(1>=$C4,F$2<=$D4),AND(F$3>=$C4,0<=$D4)),"x",""))

:) still don't works :|

I have a question ... why you put "1>=$C4" and "0<=$d4"

?

[Bob Phillips]

It works here.

1 is midnight - end of day, 0 is start of day.