Count time after midnight

I would like to count how may people are on duty each hour. If I use this format =IF(AND($a1<=BR$4,$b1>BR$4),1

A= start time
B= end time
BR= time of day ( for instance 1600or 1700)

with the shift of 1600-0230

all works fine until I get to the overnight shift were the end time is 0230 or 0330... and it registers "false" because b1< than BR4 (0230 is less than 1700 and will not count this as a valid time slot.

Can someone please offer a suggestion that would cover regular and overnight shifts? Thanks

Try like this

=IF(($a1<=BR$4)+($b1>BR$4)+($a1>$b1)=2,......

shift is 1800-0230
a=1800
b=0230
br=1700

=IF(($a1<=BR$4)+($b1>BR$4)+($a1>$b1)= 1

br=1800
=IF(($a1<=BR$4)+($b1>BR$4)+($a1>$b1)= 2

I can not find a consistant format that would cover the hours they work and count at 1 or 2 and then for hours they don't work to count as 0. Thanks

Sorry, perhaps I misinterpreted your requirement, I thought you wanted to base an IF statement on that test. If you want to return a 1 when the BR time is between A1 and B1, a zero otherwise, just use this formula

=(($a1<=BR$4)+($b1>BR$4)+($a1>$b1)=2)+0

so If a1 = 18:00 and B1 = 02:30 then 19:00 or 01:00 in BR4 will give a 1 but 03:00 will give a zero, is that what you need?

Of course the formula still works if your shift doesn't pass through midnight....

Worked great thanks.

I don't want to hijack this thread but I also didn't want to start a new one for the same type problem. If I should, moderator please delete this response and I will create a new thread.

I was not able to get the formula to work as described, but perhaps I am not looking at it correctly. I have attached a sample workbook of what I am trying to accomplish and would appreciate some tweaking of the formula.
The formula works for shifts between 0000 and 2400 but does not when crossing over midnight.
I would also like to understand the logic behind the formula. I know it has to do with T/F conditions, but I'm confused about the "2" in the formula.

Thanks,
TC