Duty Time Calculation

J

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
 
M

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
 
M

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
 
J

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
 
M

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

Mike
 
J

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

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

JB Bates

Thanks Biff -- worked like a charm!!

T. Valko said:
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rota scheduling 0
Need formula for calculating time 2
Calculating elapsed duty hours 0
Pivot Table help 0
If Formula 15
how to set the fix time duty 3
link formulas 1
Assigning shift to Time Intervals using IIF 6

Top