Help a Pilot Version 2

[freeze3192]

Hello,

So I posted here about a year ago looking for some help in making my schedule better. The help was awesome and I have been using the tool that was created by one of the users here ever since.

I do have another question that I'm pretty sure can be solved using an automated script or something similar in Excel.

I'm a pilot for an airline. Each month we get whats called a bid packet. The bid packet has trips in it, which can be anywhere from 1 day trips to 5 day trips. We rate the trips based on efficiency, which is the number of hours that the trip is worth divided by the number of days you are out. For example, if you have a 4 day trip worth 20.00 hours, the efficiency is 5.0. We can fly a maximum of 8 hours per day, so the trips that are closer 8.0 for efficiency are the best to pick as they result in more time off at home (contrary to popular belief we don't all make 300k and only work 4 days a month ). The problem is that the bid packets don't come with the efficiency number already figured out.

I've boiled the bid packet down to this excel file:

BID 9-1_original.xls

Basically, what I'm trying to do with this attachment is to take the "CREDIT" of a certain trip and divide it by the number of days the trip spans. The way to determine how many days a trip is out for is rather simple. The maximum number in that row is the number of days that the trip is away from base. For example, in row 2 of the above excel spreadsheet, you should see the trip LL1*LL2*LL3*LL4*LL5. This is a five day trip. The credit it is worth is 26.21 hours. If we divide 26.21 by 5, you get 5.242. That is the efficiency of that trip.

If you go to row 115, this is where it gets a little tricky (or maybe not - you guys are much smarter than me ) you will see the trip FH. This is a day trip, and it is only 1 day of work. So it's efficiency would be 7.96 (credit of 7.96/1 day of work).

So basically, what I'm trying to get help doing is creating a script that will come up with the efficiency value and put it in a column next to credit so I can sort the trips by efficiency value (CREDIT/Number of trip days).

This is essentially the final product that I'm looking for, except I did the math myself and it took forever :

BID 9-1(20.xls

I do appreciate any help that you might be able to give!

[shg]

The trips don't have the dates in them -- how can you calculate hours per day?

[freeze3192]

The highest number that occurs in the trip name is how many days the trip is worth.

Example: In row 2, LL1*LL2*LL3*LL4*LL5 is a 5 day trip. In row 6, LE1*LE2*LE3*LE4 is a 4 day trip. Trips that have no number in the trip name are considered 1 day trips. The total hours of the trip (total flying hours) are listed in the credit column. Credit is just a fancy way for us to say hours of flight time.

[shg]

So does an asterisk demarcate days, or is it the highest number? What's the = sign mean? And the tilde? Do they matter?

And why are some report time entries like "KC3 18:57" instead of just a time?

Maybe just dump the whole explanation out at once?

[freeze3192]

The asterisk indicate that you are "overnighting" somewhere else other than your base. No asterisk means that that particular day is the last day of that trip. Hence why day trips have no asterisk as you do not overnight anywhere but your base. The = sign indicate that it's whats called a continuous duty overnight and that it's not a typical trip in the traditional sense. It's a bit complicated, but the efficiency rule still applies. Number of credit hours divided by total days in the trip equals efficiency.

I actually just saw those report times that had the end of the trip name in it. I believe that's a formatting error and I'll have to correct it obviously before any script can run.

It's a overly complicated process which is unnecessary IMO but that's the way the airlines have been doing it forever and they are afraid to change their ways. This is basically it in a nutshell. Each month around the 1st, a bid packet comes out for the next month's flying. The bid packet takes all of the flights that the airline has for that months and assigns them to "trips" in order to crew the flights. The bid packet is a massive word document with all of the details on each flight, etc. I've boiled it down to this excel spreadsheet thanks to the help I received on this forum last year.

The trips come in 1, 2, 3, 4, and 5 day trips. Each trip is assigned a credit value which is how much flight time is scheduled to occur in that trip. We are regulated by the FAA to a maximum of 8 hours of flight time per day so that is why the efficiency has a maximum of 8 hours.

We build our schedules by seniority. Our company requires us to bid a minimum schedule of 85 hours per month. The "bidding", as we call it, starts with the most senior person and they picks the trips that they want to create a schedule that equates to 85 hours or more. Because we are away from home all the time when we are working, the idea is to fly the most efficient trips that you can (i.e. work more at work) in order to gain more days off at home. For example if I pick 5, three day trips each worth at least 17 hours of credit that will equate 85 hours of work. 5 three day trips in a 30 day month results in 15 days of work, 15 days off. The trick is to maximize your time off to get more time off. The key to that is knowing the efficiency of each trip and being able to pick the higher efficiency trips (the ones closer to 8 hours) versus a low efficiency trip.

In the example above, the 3 day trips that I picked that were worth 17 hours are considered "low efficiency." This is because their efficiency value is only 5.67 which is a long ways to 8 (max efficiency). So instead if I pick 4, three day trips with a minimum value of 24 hours (96 total hours for the month, meets the minimum of 85). I gain 3 more days off than in the original example. 4, three day trips = 12 days of work, 18 days off.

Clear as mud? I know it probably doesn't make a whole lot of sense, as it took me quite a bit of time to understand it as well. Sometimes I'll still be scratching my head at the things that go on at the airlines.

I've attached a newer version of the bid packet excel file. It's for a different month so the trips are a bit different from the first two. It also corrects the formatting errors and removes the trips with the = sign to prevent confusion.

10-1 Bid.xlsx

[freeze3192]

So does an asterisk demarcate days, or is it the highest number? What's the = sign mean? And the tilde? Do they matter?

To add a bit, it's just the highest number. The asertisk has no effect on the number of days a trip is, nor does the = and ~ signs. They don't matter in this instance.

[shg]

OK, Maybe ...

	A	B	C	D	E	F	G
1	TRIP NAME	REPORT	RELEASE	CREDIT	Days	Eff
2	LP1*LP2*LP3*LP4*LP5	07:50	15:25	27.76	5	5.55	E2 and down: =LEN(A2)-LEN(SUBSTITUTE(A2,"*", ""))+1
3	LR1*LR2*LR3*LR4*LR5	07:54	18:45	27.71	5	5.54	F2 and down: =D2/E2
4	LS1*LS2*LS3*LS4*LS5	07:54	18:45	27.71	5	5.54
5	LN1*LN2*LN3*LN4*LN5	06:54	20:16	27.25	5	5.45
6	LQ1*LQ2*LQ3*LQ4*LQ5	07:54	19:54	26.95	5	5.39
7	LF1*LF2*LF3*LF4	08:15	19:54	25.03	4	6.26
8	LH1*LH2*LH3*LH4	10:49	19:54	24.46	4	6.12
9	LI1*LI2*LI3*LI4	10:49	19:54	24.06	4	6.02
10	LO1*LO2*LO3*LO4*LO5	06:54	15:06	23.76	5	4.75
11	LG1*LG2*LG3*LG4	10:49	19:54	23.18	4	5.80
12	LK1*LK2*LK3*LK4	11:32	16:04	23.18	4	5.80
13	LE1*LE2*LE3*LE4	08:15	19:54	21.75	4	5.44
14	KU1*KU2*KU3*KU4	06:54	20:20	21.50	4	5.38
15	KW1*KW2*KW3*KW4	06:54	20:13	21.06	4	5.27
16	LJ1*LJ2*LJ3*LJ4	10:51	14:54	20.33	4	5.08
17	KX1*KX2*KX3*KX4	07:16	20:16	20.11	4	5.03

Edit: Attached.

[freeze3192]

Yup! That works wonders!

I told you that you guys are much smarter than I am.

Thank you very much!