I’ve watched the EBA with some envy for the last three years. Between Overtime, Callout, Domestic Allowances, Leave and Days Off, the position of Manager hasn’t quite kept pace with the EBA. I’ve also been watching the need for a spreadsheet to calculate the Overtime, Callout and Accommodation Cancellation monies, but resisting the temptation to build one. I didn’t quite want to know how much I was missing out on.
Current Version : V2.6 27.Oct.17 : Down HERE.
– Corrected for leaving the wrong cells locked in 2.5 – no other changes.
– I really would have thought we’d be on the new EBA by this RP, but apparently not? Anyway – 2.5 extends the sheet for a couple more RP’s just in case. Esp since there’s still seems to be a lot of overtime going around, even with an aircraft in maintenance for most of this RP …
– Ok, so after some discussion at a recent EBA meeting, positioning credit/before after duties (1 hour) is going to be paid for the time being. Meanwhile the lack of any payscales after 01Jul17 has broken my spreadsheet. For some reason I didn’t plan for the 3 year EBA of 2011 to still be in force … fixed in 2.4
– After years of paying it, the company is now no longer paying a 1 hour credit for positioning before/after any other duty. Until recently, the 6 hours or so added to my day that is positioning up to BNE for Sim/Admin/etc is not worth Zero. The interpretation is that this event is “travelling” before/after a duty and not positioning under the EBA. Yeah right.- Positioning BNE/SYD/MEL no longer carries a 1 hour credit unless it’s completed as it’s own duty.
– Corrected adding error in Block Hours Total (Top RHS)
– Thanks to PM for spotting to bugs that I’ve corrected in V1.5
– Thanks to TH removed Super from Salaries to better compare Overtime with payslips in V1.6
– Now deals with Carry In/Out Flights that wrap from one RP to the next (see below) – V1.7
– Bug correction in Carry In/Out Flights & Updated to clarify that Push/Park are for Sked; Actual (was User) is for Actual Block (Tks TD) – now in V1.8
– Corrected Calc Blocks Time not showing 00:00 for midnight V1.9
– Added BNE/MEL positioning; change to allow for zero credit positioning on same day of Duty V2.0
– Added CCA/TCA/TFO Rank Selectors to incorporate appointment pay in overttime V2.1
The combination of recently updating my Tax Allowance Claim Calculator for the 2011/2012 Tax Year as well as seeing someone else’s overtime calculation sheet come past my inbox, I decided it was time to get off my tail and build one. I also decided to see how much I could test the data validation and conditional formatting functionality of Excel and turn it into a custom form like entry interface that would test and indicate both incompleteness as well as validity of entries. In the past I’ve always tried to maintain compatibility with Excel 2003, forgoing the really cool features of Excel 2007/2020 – not anymore.
VAI 777 EBA Overtime Calculation Spreadsheet.
I wanted my sheet cope with the following aspects, all in a single spreadsheet.
- Ranks : It does Captains, First Officers, Relief First Officers; Check Captains, Training Captains and Training First Officers under the EBA.
- Levels : Whether you’re Level 5 or Level 1 – you select and the sheet does the rest.
- Level Changes : On 01 July each year, your Level increments. Therefore the Overtime rate increases also. The sheet copes with this.
- Duty Select : You don’t have to know the credit hours – just select the duty for each duty day and the sheet will use the relevant EBA Credit Hours
- Leave : Having Leave impacts the Overtime Threshold, as well as the minimum number of days off required in the month – the sheet copes with this.
- Positioning : There are two types of positioning – EBA (MEL-SYD-MEL or BNE-SYD-BNE) and All Others. The EBA has the standard credit hours – the rest you have to enter. The sheet uses the default credit for EBA positioning; facilitates your entry of the Block hours for non EBA positioning (SYD/KUL/SYD).
- Ad Hoc Training : When you’re not a Check/Training Captain, but conducting Ad Hoc Training as an Instructor (NTS) – there’s a credit and payment. The sheet tracks this as well.
- Data Filtering / Validation : As much as possible, entries are checked from lists for validity (Duties, Airports, Yes/No’s, etc). Anytime this is done – there’s a list box you can click to drop and choose from.
- Summaries and Analysis : Once complete, quick reference summary at the top for Days Off; Leave; Sims; Admin; Standby/Open; Ground Duties; Credit hours (in relation to the Overtime Threshold); Callouts; Block Hours; Cancelled Accom and Ad Hoc Trainer. There’s also a list of duties with a count on the far right, and I’m playing with Pivot Tables and Charts in this one too.
- Variables ?: I’ve coded as much as I can as variables that can be changed should I need it to. I can’t see anything changing in the EBA in the next 12 months – but just in case …
Let me run you through how to use it:
1. Basic Entries.
The first things the sheet really needs to know is your Rank (Capt, FO, CRFO); Pay Level (1 … 8); and which Roster Period you are looking at (currently only from RP 2012 3/4 onwards). Note that Rank now includes choosing Check/Training Captain/First Officer since this impacts some values.
Note that the Pay Level is the one at the start of your target RP. For most of the original VAI pilots that will be level 5 from RP 9/10 2012 onwards.
Note that when you point your mouse at a cell with a little red triangle in the corner – a hint pops up. Also not that when you click into a cell for data entry – if there’s validation on the cell (such as the requirement to select from a previously established list) a small down arrow shows to the bottom right of the cell – click the example here to see.
Having chosen these variables, the initial credit threshold (it will updated as Leave days are later selected) and Initial Overtime Rate should be checked. All the Dates down the LHS should also fill in for the 56 day RP.
The?Clear Button – clicking this will remove ALL entries into the sheet (From Rank through to all the duties and Positioning/Flight entries – and There’s No UNDO!
2. Dates / Duties and Supporting Data.
You’ll now see that the Dates for the RP have pre-filled. Also the Duties Column has become Yellow – indicating required input. You must enter a duty for all the days of the RP.
Clicking next to the date you want under the Duty column displays the small drop down box arrow. While you can manually type the text for the duty (and indeed having selected a duty for the first time – typing that duty type subsequently is much faster) – initially it’s faster to click the arrow and choose from the list.
Note – There will be days when you don’t have a assigned duty, but it’s not a day off either. These are typically days down route, or the day you lose on the way back from LAX. Choose?Away Day in this case. It has no credit.
Having Selected a Duty – some of them result in a pre-fill of default hours; some require more information. Those requiring details are covered down below.
- Admin Day : Nothing Required.
- Away Day : Nothing Required.
- Flight : Details Required (From / To / Push/Park Times; or User Block Time) – See Below,
- Ground Duty : Nothing Required.
- Online (Yearly V-Learn Credit) : Nothing Required.
- Open Day : Nothing Required.
- Positioning : Details Rquired (EBA Default / User Block Hours Required) – See Below.
- Sim Instructor / Student : Nothing Required.
- Standby / Reserve : Nothing Required.
3. Callout /Cancel Accommodation.
If you were called out for the duty – you need to specify this. Remember callouts are divided into First Day and The Rest (I call them Day 2+). If you’re called out off a Day Off for a single day (say a Sim) then it’s Day 1 Callout. If called out for a series of duties of a string of Days Off – they the first day is Day 1 Callout; the rest are Day 2+ Callout.
Enter/Select Yes against the days. While you can select No – this has no impact on the calculation. You can theoretically select Yes to both D1 and D2 but the sheet will get grumpy with you …
You can also specify if you Cancelled Accommodation against a Day (usually a positioning day prior to a trip). Again ? No does nothing, Enter/Select Yes to specify.
4. Positioning.
Having selected Positioning?as the Duty Type – you now need to specify whether EBA or Other. Remember you’re Sydney based – therefore all EBA positioning is SYD-BNE-SYD or SYD-MEL-SYD. When you select one of these four sectors, the standard 1:00 credit is used. Even if you’re commuting from your MEL domicile to BNE for a trip – your official EBA/Sabre positioning is SYD-BNE.
If however you’re doing something unusual (like a KUL position or heading to AUH or LAX to position for someone going sick down route) – use?Pos OTHER?in the list box – you’ll now need to enter the Block Time for that trip under the User Block column (which colors Yellow until you do).
5. Flight Details.
For Operating Flights – you require the Off Blocks (Push) and On Blocks (Park) times to accurately calculate Credit Hours. Since almost all our flying is single sector – I’ve facilitated entering From/Push/To/Park entries to calculate the total Block Time and let you keep a record of what you did in case you want to compare with Sabre when chasing Overtime with Payroll.
The From / To fields are either typed or select from the list of stations.
The Push / Park times are entered using (a) 24 hour time; and (b) the same time zone. No good entering a 01:40 pm departure from Sydney and a 07:30 am arrival time into Los Angeles and expecting the sheet to do the rest.
Note that you can enter time as a formula in Excel, which can be handy if you want to do addition subtraction. For example if you want to enter GMT times, but only have local on the roster – then 07:25 Melbourne Time (at the moment) would be entered as (without the quotes) : “=Time(7,25,0) – Time(11,0,0) + Time(24,0,0)
Note : If you go somewhere outside the network; if you divert and have multiple sectors or do anything else unusual – use the Actual Block column to enter the total block time (for all the sectors operated on that day) to get the right credit into the calculator. A manual entry is compared to the calculated value and the larger value is used. If both Calculated and Actual values are displayed – the larger one (the one in use) will be bold.
6. Carry In / Carry Out Flights.
There is potential confusion in the way flights that wrap over the Start/End ?of the RP (Midnight). Essentially if you take of before Midnight (SYD time!) and land on the first day of a new RP – that portion of the flight since Midnight (SYD time!) until parking is the Credit Hours for the new RP. Conversely the flight time between pushing back an Midnight (SYD time!) belongs to the previous RP. The spreadsheet handles this odd decision in the following way:
Carry In Flight Time
There is an additional (purple) line at the top of the RP. This line is ONLY for flights that carry into the RP and the only Duty Type you can choose is “Flt – Carry In Credit” or “Not Applicable.” Having chosen to enter a Carry In Credit Flight – you can’t select Callout/Ad Hoc Training/Accommodation Cancel or Positioning, because (for now) these belong to the previous RP.
Having selected “Flt – Carry In Credit” the Flight Details section is enabled – with a difference. Any time you enter into the Push Time will display as MidNite SYD. You MUST enter the Park Time in SYD Local Time (even if the flight has nothing to do with Sydney – such as AUH-KUL). That portion of the flight which falls between Midnight Sydney (Local Time) and the Parking time (Sydney Local again) will be?credited?into the RP overtime calculation.
It will be easy to screw this up – Remember, only the Park Time counts, and it must be converted to Sydney Local time BEFORE you enter it into the sheet.
Carry Out Flight Time
The last line in the RP has some intelligence in it to account for flights that depart in the RP, but land in the next RP. Instead of Duty Type “Flight” – if your flight goes over Midnight Local Time Sydney (whether or not your flight has anything to do with SYD) – choose instead Duty Type “FLT – Carry Out Credit“. This will:
– Color the line purple to remind you you’re doing something different;
– Whatever you put in the Park Time – the cell will display MidNite SYD to remind you;
– You MUST enter the Push Time ?in Local SYD time – wherever the flight is going to/from.
– The Calc Block will work out the time between Push and Midnight for you.
Note : For an LA Trip that comes back over the imaginary line between the end of one RP and the?beginning?of the next – usually this means (from the point of view of?Sydney Time) – the entire return sector is in the next RP.
When All Else Fails
Remember you can always use the User Block to work out what you reckon the portion of the flight between Push/Park and Midnight?SYD is yourself!
Bringing it All Together.
Let’s look at a sample scenarios of a range of duties. We’ll look at a pilot having a Day Off at the start of the RP (how nice …); followed by an Admin Duty, then a Ground Duty,?which is both a Callout and an Ad Hoc Instructor Day; then this pilot Positions up to BNE in order to Position again the next day over to LAX (Why? Why?). Once in LAX a Standby Duty is completed, prior to operating a Flight back to MEL. An Away Day is lost during the return flight, and afterwards, a Positioning Day MEL back to Sydney (even though this pilot lives in MEL). After that a couple of Days Off, two Sims (one as Instructor, one as Student) and then off on Leave.
Sun 24.Jun [Previous RP]?: Flight – Carry In Credit
– Having departed on the last day of the previous RP, you now need to account for that portion of the flight which occurs during the next RP – defined as the time between Midnight (SYD Local) and Parking (SYD Local). Duty Type is “Flt – Carry In Credit” so the sheet knows what you’re doing, Callout etc a disabled (they belong to the previous RP) and the Flight Details has LAX, Midnite SYD (which is when this flight began, as far as the RP is concerned) and the Park Time in SYD Local.
Note that an Actual Block of 6:10 has been entered into the sheet – note also that it’s the Calculated?6:15 that’s in bold and therefore is being used by the sheet (the larger figure)
Mon 25.Jun : Away Day
– This is the day the pilot returned from LAX. It’s not a duty, not a day off – but it needs to appear in the sheet somehow – hence Duty Type “Away Day”
Tue/Wed 26/27.Jun?:?Days Off
– No Credit, but increases the Day Off count at the summary at the top of the sheet.
Thu 28.Jun : Admin Duty
– Notice the 5:00 hours credit in the Credit Hours / Duty?&?EBA columns, also added to the Cumulative Credit column.
Fri 29.Jun : Ground Duty
– Apart from being a Ground Duty – this is an Ad Hoc Training?event for which the pilot was Called Out (first day). Looking across you can see 5:00 hours in the Credit Hours / Duty & EBA columns, also added into the Cumulative Credit.?In the far right is the Callout Pay as well as a payment for the Ad Hoc Training duty.
Sat 30.Jun : Positioning to BNE
– Here the pilot’s positioning up to BNE. A default positioning credit of 1:00 hour is calculated under the EBA Block and then repeated under the Credit Hours / Position?&?EBA columns. Cumulative Credit also includes this hour.
Sun 01.Jul?: Operating BNE-LAX
– First flight for the RP is BNE-LAX with the Push/Park times entered in UTC. The Calc Block is copied under the Credit Hour sections, including making a big difference now in Cumulative Credit.?This last column has a blue background, because progressively, we’re on track now to make some Overtime (depending on what happens in the rest of the month) – which you can see by comparing?Cumulative Credit?with?Credit Target.
Note that you’ve entered Actual Block hours for this sector as 13:20. Since this is larger than the calculated value, it’s bold and is used in the calculation of your hours/money.
Mon 02.Jul : Standby/Reserve
– Standby in LAX. Note the 5:00 hours under Credit Hours / Duty Type / EBA, etc.
Tue 03.Jul : Positioning LAX-???
– Something’s clearly gone wrong because this pilot is positioning back from LAX. Positioning / Sector is selected as Pos OTHER and a User Block time of 13:50 hours has been entered manually. This is halved by the EBA under Credit Hours / Position & EBA columns. Still well ahead on Cumulative Credit though, with the blue background showing.
Wed/Thu 04/05.Jul?: Sick
– That explains positioning home. Worth noting that Sick (apart from having no credit, like Leave) also doesn’t impact your overtime threshold either. Going sick has a big impact on monthly productivity.
Fri 06.Jul : Sim Instructor
– Our pilot is a Trainer! Having selected Sim Instructor under Duty Type, 6:00 hours credit is assigned under Credit Hours / Duty Type & EBA / Cumulative Credit, etc. Note out pilot was called out for this duty.
Sat 07.Jul : Sim Student
– Peculiarly, our Instructor is now a Sim Student, with 5:00 hours under Credit Hours / Duty Type & EBA. Again our pilot was called out (second day) for this duty.
Sun-Tue 08-10.Jul?: Open Days
– Open days have a reduced credit (3:00 hours) showing under Credit Hours / Duty Type / EBA
Sun 11.Jul : Leave
– Our pilot now heads off on Leave. While there is no credit for leave, instead the Credit Threshold is reduced down for each day of Leave assigned in the RP.
EBA Overtime Calculator – Summaries
I’ve built several summaries into the spreadsheet. Make sure you’ve finished all entries (“Ok” showing in the Top LHS corner of the sheet) before looking at them!
Ok on the far left hand side means all dates have valid duties / duty details and as far as the sheet can determine – all is Ok.- Days Off shows red because (no Leave assigned) – the EBA requires 20 days off, but this pilot has 19. That’s ok – he’s Management and only requires 16. The number to the right of days off is the minimum, adjusted by the number of leaves days assigned in the RP.
- Leave – None assigned in the RP
- Sim (I) : As a Sim Instructor – 5 sessions
- Sim (P) : As a Sim Student (Pupil) – 0 sessions.
- Admin : 19 days.
- Standby : 1 duty (down route or otherwise).
- Open Days – None.
- Ground Duties : 4 Days (other than Admin).
- Total Hours : 208:40 Credit Hours
- Cutoff : 165:00 for this RP (no Leave).
- Overtime Hours : 43:40 (… here it comes …)
- Overtime : $11,713.48
- Callout # : First Day Callouts – 2; Subsequent Day Callouts – 4.
- Block Hours : 56:40 operating the aircraft.
Error Checking
I have some error checking, entry requirement flagging, and flawed entry logic in the sheet (very experimental).
If you see the big Error flag in the top left hand corner – this indicates an error somewhere else in the sheet. Either you haven’t entered duties against all days in the RP – or one of the duties has a required field missing.
On the far right hand side of each row, a Ok / Error flag indicates whether the row is complete.
Within the sheet, incomplete row data is shown by Yellow (needs input) or Red (flawed input) cells.
The End – Let Me Know
If you find it useful, let me know in the comments here! If you have any questions – ask them here so we can all benefit from the questions/answers. If you find a bug – report it here please, I’ll investigate and update the sheet.
Ken.
If you find my content useful and are in a position to do so – I would appreciate a contribution to my PayPal account (ken.pascoe@gmail.com) – If you use the Friends and Family feature in PayPal it reduces the charges to the transfer. Please note that when sending money in this way you are removing any form of purchase protection, which is not relevant to a contribution of this type anyway.