Time calcs
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.
|