airline standby worksheet

[khad]

hi all again

after sorting 1 problem out i have found that i have another calculation
problem, if anyone can help with a formula please.


Local Time of Starts
Sectors
1 2 3 4 5 6 7 8 or more
06:00-07:59 13:00 12:15 11:30 10:45 10:00 09:30 09:00 09:00

08:00-12:59 14:00 13:15 12:30 11:45 11:00 10:30 10:00 09:30

13:00-17:59 13:00 12:15 11:30 10:45 10:00 09:30 09:00 09:00

18:00-21:59 11:15 10:30 09:45 09:30 09:00 09:00 09:00 09:00

22:00-05:59 11:00 10:15 09:30 09:00 09:00 09:00 09:00 09:00






Start of SBY 11:00 FDP Allowed 12:15

Start of FDP 17:00 Total Duty Allowed 18:15

No, of Sectors 2 Max Finish Time 05:15

sorry the sheet details havent quite pasted here very well, but the problem
i have is when i wish to start my standby in a differant time bracket e.g
08:00-12:59, now i have to tell the fdp allowed collum to recognise the duty
times allowed within that bracket e.g 2 sectors allowable fdp = 13:15. the
fomula i have in fdp allowed cell at the moment is this
=IF(D29=E9,E10:E18,IF(D29=F9,F10,IF(D29=G9,G10,IF(D29=H9,H10,IF(D29=I9,I10,IF(D29=J9,J10)))))).
which only covers the times from 06:00-07:59 sectors flown with allowable
duty time e.g 2 sectors = 12:15.

thanks for any help and this would then solve the worksheet.

 

[Sandy Mann]

Again I think that you are going to have to explain your problem a bit more
because speaking personally what you have posted is meaningless to me. What
are Secors? What do the times repersent? What result are you looking for?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[khad]

hi sandy

sectors are the flights e.g london-new york =1 sector new york - lon =2
so what i am trying to do is calculate what duty hours i can do if called
out from standby, which a duty we carry out at home waiting to be called or
not. but during the time blocks shown there are 2 veriables the sectors that
you could be asked to fly, then the duty limitation placed on you during the
time brackets, i need to get the allowed fdp cell to recognise all the
differant duty limits relating to the sectors, eg 06:00-07:59 4 sectors =
10:45 10 hrs 45 mins of duty, now the formula that i had wrote took care of
the 06:00-07:59 times, but after you solved the other problem i had i soon
realised when i moved time brackets eg 22:00-05:59 the duty time did not
match the time for the amount of sectors eg 4 sectors in that time bracket =
09:00 9 hrs duty, because i have written the formula wrong.

i think this is a tough nut to crack, a long formula, way beyond my simple
knowledge of excel.

anyway thanks for your help so far

 

[Sandy Mann]

Khad,

It has gone Midnight where I live and so I am off to bed just now. If no
one else responds I will have a look at it tomorrow.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Hi Khad,

Even with a night's sleep I am still having difficulty understanding what it
is that you are trying to do. I guess from:

that you are having problems when the times cross midnight and you want to
calculate the number of hours from 22:00 to 5:59 the next morning.. If so
then with 22:00 in A1 and 05:59 in B1, try using:

=MOD(B1-A1,1)

This will give you the correct answer whether or not the time does cross
midnight so it can be used for all calculations.

If I am wrong then post back giving as much datail as possible including the
fomulas that you are using and the layout of your data.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[khad]

hi sandy

no the formula i need has nothing to do with crossing midnight. what i need
is a formula that just recognises the differant duty times applicable to the
sectors e.g 06:00-07:59 = 13:00 for 1 sector 12:15 for 2 etc, and this should
refect in the fdp allowed cell, so that when i say have a stanby from
13:00-17:59 4 sectors, the fdp allowed cell should pick up 10:45 10 hrs 45
mins. i was only able to figure out how to tell th fdp allowed cell the
06:00-07:59 duty times and sectors through using this formula
=IF(D29=E9,E10:E18,IF(D29=F9,F10,IF(D29=G9,G10,IF(D29=H9,H10,IF(D29=I9,I10,IF(D29=J9,J10))))))
but couldnt work out how you tell the fdp allowed cell all the other time
blocks and sector duty times.

many thanks for all your time looking at this.

 

[Sandy Mann]

I think that light is beginning to dawn on me:

With your table in A2:I6

A B C D E F G H
2 06:00-07:59 13:00 12:15 11:30 10:45 10:00 09:30 09:00 09:00
3 08:00-12:59 14:00 13:15 12:30 11:45 11:00 10:30 10:00 09:30
4 13:00-17:59 13:00 12:15 11:30 10:45 10:00 09:30 09:00 09:00
5 18:00-21:59 11:15 10:30 09:45 09:30 09:00 09:00 09:00 09:00
6 22:00-05:59 11:00 10:15 09:30 09:00 09:00 09:00 09:00 09:00

With 13:00-17:59 in cell D28 and 4 (for the sectors) in F29 then:

=VLOOKUP(E29,A2:I6,F29+1,FALSE)

will return the 10:45 you are looking for when the cell is formatted as
time. Is this what you are looking for?

If not then, provided you are not using XL2007, you can send me a sample
Workbook to illustrate your problem. Change the part of my address after
the @ sign as it says in my signature.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

For the archives the VLOOKUP() formula was what was required.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk