Duty Time Calculation
Thanks Biff -- worked like a charm!!
"T. Valko" wrote:
Try this:
Start times in column C starting in cell C2
End times in column D starting in cell D2
Enter this formula in E2 and copy down as needed:
=IF(D2="","",ROUND(MOD(LOOKUP(2,D$2:D2)-LOOKUP(2,C$2:C2),1)*24,2))
--
Biff
Microsoft Excel MVP
"JB Bates" wrote in message
...
Hi Mike
Thanks again. I was able to enter the array formula with success. this
may
seem minor, but since you are so knowledgable i thought you might know.
Here is what I got when I did this
Leg 1 10/6/09 13:00 17:00 4
Leg 2 10/7/09 8:00 10
Leg 3 10/7/09
Leg 4 10/7/09 18:00
Leg 5 10/9/09 17:00 10.5
Leg 6 10/9/09
Leg 7 10/9/09
Leg 8 10/9/09 3:30
Leg 9
Leg 10
IS there a way to have it display the total duty time on the row that
houses
the duty off time?
Really, really appreciate all your help with this!! JB
"Mike H" wrote:
Hi,
The formula I gave you does exactly that and here it is modified for
times
in columns C&D.
Don't forget to array enter it, if your not sure read my first post again
on
how to enter an array formula.
=IF(C2="","",(INDEX(D2:D100,MATCH(TRUE,D2:D100<0, 0))-C2+(INDEX(D2:D100,MATCH(TRUE,D2:D100<0,0))<C2))*2 4)
Mike
"JB Bates" wrote:
Hi Mike
The max legs I cna have is 10. But I could potentially end up with the
following scenario. can I adjust this formula to fit? I just dont
know when
duty on / duty off will happen in advance - so i want a formula that
can
accomodate when it finds data.
possible example
A B C D
E
Duty On Duty Off
Total Duty
Leg 1 10/6/09 13:00 17:00
4.0
Leg 2 10/7/09 08:00
Leg 3 10/7/09
Leg 4 10/7/09 18:00
10.0
Leg 5 10/9/09 17:00
Leg 6 10/9/09
Leg 7 10/9/09
Leg 8 10/9/09 03:30
10.5
Leg 9
Leg 10
THANKS in Advance - I really appreciate your help. JB
"Mike H" wrote:
Hi,
In your sample date you show 3 legs and I've assumed this is the max
so only
look down 3 rows in column C. If it could be more then change c4 in
the
formula to C5 for 4 rows. This is an array formula. See below on how
to
enter. Put it in (say) d2 array enter and drag down. Every time it
finds a
valu in column B it looks for the finish time in column C
=IF(B2="","",(INDEX(C2:C4,MATCH(TRUE,C2:C4<0,0))-B2+(INDEX(C2:C4,MATCH(TRUE,C2:C4<0,0))<B2))*24)
This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly and look in the formula
bar
'then Excel will have put curly brackets around the formula {}.
'You can't type these yourself and If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
Mike
"JB Bates" wrote:
I want to calculate duty time, but where the crew may not be on and
off duty the same leg - can you advise how i would do that?
Duty On Duty Off
Leg 1 13:00
Leg 2
Leg 3 23:30
how can I have it look at the duty off column and keep looking till
it finds
a time? and getting the result as 10.5 Hours
thanks
|