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.

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


Replace @mailinator.com with @tiscali.co.uk


"khad" wrote in message
...
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.

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

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


Replace @mailinator.com with @tiscali.co.uk


"khad" wrote in message
...
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

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:

realised when i moved time brackets eg 22:00-05:59 the duty time

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"khad" wrote in message
...
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

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.

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


Replace @mailinator.com with @tiscali.co.uk


"khad" wrote in message
...
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.

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

--

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"khad" wrote in message
...
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.