If rostered hours are 2300 to 0600.
How can I figure how many of those hours fall between 2000 and 0600.
Any help most appreciated thanks.

Hi!

Believe it or not, this is very complicated. The only way I know how to get
this to work is by including dates.

A2 = start date/time = 4/4/2006 23:00
B2 = end date/time = 4/5/2006 6:00
D2 = shift start date/time = 4/4/2006 20:00
E2 = shift end date/time = 4/5/2006 6:00

=IF(B2<D2,0,IF(A2E2,0,IF(B2E2,E2,B2)-IF(A2<D2,D2,A2)))/(B2-A2)*(B2-A2)

Format the cell as h:mm for a return of 7:00.

If you want the result as a decimal value:

=IF(B2<D2,0,IF(A2E2,0,IF(B2E2,E2,B2)-IF(A2<D2,D2,A2)))/(B2-A2)*(B2-A2)*24

Format the cell as GENERAL for a return of 7.

Biff

"srb" <u20509@uwe wrote in message news:5e4c761de7da2@uwe...
If rostered hours are 2300 to 0600.
How can I figure how many of those hours fall between 2000 and 0600.
Any help most appreciated thanks.