Forum Discussion
Operations Dashboard in Excel
Ok, so I have been tasked to build an operational dashboard for an airline maintenance planning and tracking.
I have a dataset downloaded from our ERP system that lists down the aircraft tails, the workorder number, tasks in each workorder, manhours for each task, city, site(hangar or line) and the start date and end dates. There are codes that are assigned for each category of workorder, whether it is a C-Check, Line or transit.
In the current scenario, we use a flight operations tracking software that gives us a hangar forecast, but then we have to get the dataset (as mentioned above) and then build a report daily to show the tails assigned for each port and then the total manhours. The report looks something similar to what you see below.
Now, instead of doing it daily manually, I want to automate the process. So far, I have been able to sum the total manhours for the day, get the tails assigned for each port and location, and achieve some sort of conditional formatting to distinguish between different types of checks - green for heavy, yellow for transit and so on.
What I have been unable to achieve is the aircraft is scheduled for two days grounding in the hangar, then the cells on both days should align together. As you can see in the image above, VH-AA6 has maintenance on the 8th and 9th of March, but the cells are not aligned. I tried to find a difference between the start and end date and create a helper column to assign a priority, but it didn't work. I have spent countless hours on Chatgpt to come up with a solution, but all efforts went in vague. I have seen a similar excel sheet elsewhere, but I couldn't extract the formulas or the logic since it was heavily protected. In the end I want to add a search bar and a to find a Rego/ Tail by typing in the search field and highlight in the sheet quickly.
The main aim is the cell alignment for the consecutive dates. So lets say AA6 is occupying D2 on Day 8, then on the Day9 AA6 should pop up in E2. Any other aircraft on the Day9 with a day's grounding may appear in E1, or next available empty cell.
2 Replies
HelloSiddhi817,
The alignment issue occurs because each date column is evaluated independently, so Excel places aircraft in the next available row for that specific day. This causes aircraft that span multiple days to appear on different rows.
To keep the aircraft aligned, the formula must check whether the column date falls between the aircraft StartDate and EndDate.
If your ERP data is stored in a table with columns Tail, StartDate, EndDate and ManHours, place the schedule dates across row 1 and list the aircraft tails in column A.
In the schedule grid use a formula such as
=IF(SUMPRODUCT((Table1[Tail]=$A2)(D$1>=Table1[StartDate])(D$1<=Table1[EndDate])),$A2,"")
A2 contains the aircraft tail and D1 contains the column date.
If the column date falls between the StartDate and EndDate the aircraft tail is returned. Because the aircraft remains tied to the same row, it automatically appears in the same row across consecutive days such as VH-AA6 appearing on both 8 Mar and 9 Mar.
Daily manhours can be calculated with
=SUMPRODUCT((D1>=Table1[StartDate])*(D1<=Table1[EndDate])*Table1[ManHours])
You can also add a search cell and apply conditional formatting with
=ISNUMBER(SEARCH($B$1,D2))
Typing a tail number will highlight all matching cells in the schedule.
Microsoft documentation
https://support.microsoft.com/en-gb/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e
I'd like to ask what airline you're working for so that I can avoid ever booking a flight with that airline. I say that partly in jest, but also to make a point: it worries me that you're trying to manage something so critical by designing a spreadsheet to track it....surely there already exists software to manage this workflow and all other associated matters.
If this is a serious request, you should be going to a consultant who works professionally on this type of thing and who is well compensated for that work (as contrasted with volunteers here who just enjoy helping people get over some of the hurdles of learning Excel). Definitely you shouldn't be using ChatGPT, not for something this critical.
