Duty Time Calculation

[JB Bates]

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

 

[Mike H]

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

 

[Mike H]

Hi,

On reflection it doesn't have to have a limited amount of rows to look down

=IF(B2="","",(INDEX(C2:C100,MATCH(TRUE,C2:C100<>0,0))-B2+(INDEX(C2:C100,MATCH(TRUE,C2:C100<>0,0))<B2))*24)

This now look for up to 99 rows

Mike

 

[JB Bates]

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]

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(D2100,MATCH(TRUE,D2100<>0,0))-C2+(INDEX(D2100,MATCH(TRUE,D2100<>0,0))<C2))*24)

Mike

 

[JB Bates]

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

 

[T. Valko]

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$22)-LOOKUP(2,C$2:C2),1)*24,2))

 

[JB Bates]

Thanks Biff -- worked like a charm!!

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$22)-LOOKUP(2,C$2:C2),1)*24,2))

 

[T. Valko]

You're welcome. Thanks for the feedback!