Help creating chart and formulas for un-utilized maintenance time

Hi,

I'm trying to create a chart/formulas to display/calculate in-utilized maintenance time for a continuous maintenance operation.

Data:

A set number of 'lanes' where maintenance takes place.
A potentially endless list of aircraft, which lane they were worked in and start/end dates.

Aircraft Lane Start Date End Date
Aircraft 1 Lane 1 Start Date 1 End Date 1
Aircraft 2 Lane 2 Start Date 2 End Date 2
Aircraft 3 Lane 1 Start Date 3 End Date 3
Aircraft 4 Lane 3 Start Date 4 End Date 4
Aircraft 5 Lane 1 Start Date 5 End Date 5
Aircraft 6 Lane 2 Start Date 6 End Date 6
Aircraft 7 Lane 3 Start Date 7 End Date 7


Objective:

Allow the user to enter a specified date range
Calculate how many days each lane was unused during that time period
Display these in a chart like the one attached (like a Gantt chart but one row per lane, so multiple tasks per row).


Complications:

Occasionally a lane will have 2 aircraft in work at the same time (new one inducted a few days before the previous one is complete).
That 'double stack' time does not negate other periods where a lane is empty when calculating unused lane days.
For the chart to work I'm assuming this means I need an 'overflow' row too.


Really appreciate any help you can provide!

Since no one has responded, I will venture to respond.

I don't know how 2016 builds its built in Gantt charts. In older versions, Gantt charts were basically stacked bar charts. So, worst case scenario would be to build your Gantt chart as a stacked bar chart.

They key to a lot of charts (including this one, IMO) is to get the spreadsheet right, then the chart is easy. I am not sure if you will be able to build the table or exactly what you will have trouble with. In a broad overview, here's what I would expect.

1) Using any workable combination of lookups and other functions, build a table like this:

Please Login or Register  to view this content.

Note that the starting date is a date, but the other values represent number of days. You will need to use lookup functions and subtractions and such to pull out the start and end dates from your main table and calculate the number of days for each occupied/unoccupied column. For now, I will assume you can do that. If not, then let us know and we'll focus on building this table.

2) Select that table and insert a stacked bar chart. If Excel interprets the data series wrong, execute a "switch rows/columns" or edit the data series until the data series are correctly defined. Then format the chart elements as desired (unoccupied series are made invisible, and all occupied series are formatted the same, date number format for the horizontal axis, and others as desired).

Is that something you can work with?