 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008airline standby worksheet
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?  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > hi all again > [quoted text clipped - 32 lines] > > 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. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > hi sandy > [quoted text clipped - 20 lines] > > 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. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Khad, > [quoted text clipped - 27 lines] >> >> 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. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > hi sandy > [quoted text clipped - 13 lines] > > many thanks for all your time looking at this. For the archives the VLOOKUP() formula was what was required. SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I think that light is beginning to dawn on me: > [quoted text clipped - 37 lines] >> >> many thanks for all your time looking at this. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.