Help with time formula, Excel 97

[nugget]

Please see formula at bottom of problem, and attached spreadsheet.

In the attached spreadsheet, the math in the formula works perfectly
for column “AT” as written. I am using routing codes in the formula
for held away to reflect numbers 101 to 300. My goal here is to add a
reference column, “S” (Dead Head While on Held Away Y or N), to the
formula so if a crew is called on a trip using routing code (Q) of 101
to 300, the formula will tag staying on held till departing.
Currently, the formula is tagged so that if a code of only 200 or only
300 is tagged this will occur. I need ALL codes from 101 to 300 to be
tagged, thus adding column “S”.

Here is an explanation of how held at the away from home terminal
works.

We have jobs that are double ended. In other words, crews are home
based at station “San Francisco” (their home terminal (N), and “New
York”, their home terminal (N). Crew (San Francisco) goes to New York
and back. Crew (New York) goes to San Francisco and back. Going from
San Francisco to New York (of vise versa) constitutes a trip.

They go on duty at their home terminal (N) on date (A) at terminal (D)
at x time (E) and depart on their trip at x time (F) on a run
designated by routing codes (Q) of 101 to 300 inclusive. They arrive
at the away terminal at time x (H) and go off duty at time x (I).

They go back on duty at the away point on date (A) at terminal (D) at x
time (E) and depart on their trip at x time (F) on run (Q). They
arrive at terminal at x time (H) and go off duty at x time (I).

Held at the away terminal dictates that if you are held at the away
terminal for more than 16 hours after going off duty, you get paid.
There are three scenarios that dictate this pay:

1) On runs that do not get continuous pay at the away terminal (I have
designated these runs to have column Q tagged with numbers of 101 to
200, each representing a certain position worked and job route). Held
ceases after being on pay for 8 hours (24hrs -16 = 8 hrs paid). So, if
a crew were to be off duty at the away terminal for 3 days (highly
unlikely, but possible), they would get paid 24 hours for the 72 hours
they were there [(24-16) + (24-16) + (24-16) = 24], pay ceasing after
going back on duty.
2) On runs that get get continuous pay at the away terminal (I have
designated these runs to have column Q tagged with routing codes 201 to
300. they stay on pay until going on duty at the away terminal, no
matter how long they are there. So, if a crew were to be held at the
away terminal for 3 days (highly unlikely, but possible), they would
get paid 56 hours [72-16=56], pay ceasing after going back on duty.
3) In both cases (1 & 2 above), if a crew were to dead head from the
away terminal (see column “S”), held would not cease until departing
(F), keeping in mind continuous held (2 above) and non-continuous (1
above).

A = Date
D = On Duty Place
E = On Duty Time
F = Departure Time
H = Off Duty Place
I = Off Duty Time
Q = Routing Code
1 to 100 = No Held Away
101 to 200 = Straight Held Away
201 to 300 = Continuous Held Away
N = Home Terminal
S = Dead Head while on Held Away, Y or N

This formula is set up to reference row 5.


=IF(ISBLANK(A5),"",PRODUCT(H4<>N4,D5=H4,Q5>100,INT(A5+E5*(Q5<101)+F5*(Q5=101)-A4-I4-(I4<E4))*8/24+MAX((A5+E5*(Q5<101)+F5*(Q5=101)-A4-I4-(I4<E4))-INT(A5+E5*(Q5<101)+F5*(Q5=101)-A4-I4-(I4<E4))-16/24,0))+PRODUCT(H4<>N4,D5=H4,Q5>101,(A5+E5*(Q5<300)+F5*(Q5=300)-A4-I4-(I4<E4))-16/24))


=IF(ISBLANK(DATE),"",PRODUCT(PREVIOUS OFF DUTY PLACE<>HOME TERMINAL,ON
DUTY PLACE=PREVIOUS OFF DUTY PLACE,ROUTING CODE>100,INT(DATE+ON DUTY
TIME*(ROUTING CODE<101)+DEPARTURE TIME*(ROUTING CODE=101)-PREVIOUS
DATE-PREVIOUS OFF DUTY TIME-(PREVIOUS OFF DUTY TIME<PREVIOUS ON DUTY
TIME))*8/24+MAX((DATE+ON DUTY TIME*(ROUTING CODE<101)+DEPARTURE
TIME*(ROUTING CODE=101)-PREVIOUS DATE-PREVIOUS OFF DUTY TIME-(PREVIOUS
OFF DUTY TIME<PREVIOUS ON DUTY TIME))-INT(DATE+ON DUTY TIME*(ROUTING
CODE<101)+DEPARTURE TIME*(ROUTING CODE=101)-PREVIOUS DATE-PREVIOUS OFF
DUTY TIME-(PREVIOUS OFF DUTY TIME<PREVIOUS ON DUTY
TIME))-16/24,0))+PRODUCT(PREVIOUS OFF DUTY PLACE<>HOME TERMINAL,ON DUTY
PLACE=PREVIOUS OFF DUTY PLACE,ROUTING CODE>101,(DATE+ON DUTY
TIME*(ROUTING CODE<300)+DEPARTURE TIME*(ROUTING CODE=300)-PREVIOUS
DATE-PREVIOUS OFF DUTY TIME-(PREVIOUS OFF DUTY TIME<PREVIOUS ON DUTY
TIME))-16/24))

=IF(ISBLANK(DATE),"",PRODUCT(PREVIOUS OFF DUTY PLACE<>HOME TERMINAL,ON
DUTY PLACE=PREVIOUS OFF DUTY PLACE,ROUTING CODE>100,

This sets up the parameters for the formula. If the crew went off duty
then back on duty at the away terminal, and the routing codes are
greater than 100 (any routing code 0f 100 or less is NOT entitled to
held at the away terminal), then held at the away terminal would be
calculated.

Below is where I get lost.
INT(DATE+ON DUTY TIME*(ROUTING CODE<101)+DEPARTURE TIME*(ROUTING
CODE=101)-PREVIOUS DATE-PREVIOUS OFF DUTY TIME-(PREVIOUS OFF DUTY
TIME<PREVIOUS ON DUTY TIME))*8/24


+MAX((DATE+ON DUTY TIME*(ROUTING CODE<101)+DEPARTURE TIME*(ROUTING
CODE=101)-PREVIOUS DATE-PREVIOUS OFF DUTY TIME-(PREVIOUS OFF DUTY
TIME<PREVIOUS ON DUTY TIME))


-INT(DATE+ON DUTY TIME*(ROUTING CODE<101)+DEPARTURE TIME*(ROUTING
CODE=101)-PREVIOUS DATE-PREVIOUS OFF DUTY TIME-(PREVIOUS OFF DUTY
TIME<PREVIOUS ON DUTY TIME))-16/24,0))


+PRODUCT(PREVIOUS OFF DUTY PLACE<>HOME TERMINAL,ON DUTY PLACE=PREVIOUS
OFF DUTY PLACE,ROUTING CODE>101,(DATE+ON DUTY TIME*(ROUTING
CODE<300)+DEPARTURE TIME*(ROUTING CODE=300)-PREVIOUS DATE-PREVIOUS OFF
DUTY TIME-(PREVIOUS OFF DUTY TIME<PREVIOUS ON DUTY TIME))-16/24))


+----------------------------------------------------------------+
| Attachment filename: timebook work in progress.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=353499|
+----------------------------------------------------------------+