Count days between aircraft type landings

[Rich Ard]

Hello,

I'm an analyst for and airport. I would like to count number of days between the landings of different aircraft types (cargo aircraft or freighters). The data does not come in clean.

My first column is dates. The following columns are aircraft types, but each day the same aircraft don't necessarily land each day.

e.g.

1/1/16, AN225, AN124, B747-F, B777F
1/2/16, B747-COMBI, B777F, AN124, B737-COMBI
1/3/16, B747F, AN225, B737F, A320F

Et cetera

I have several years of data. Data is daily aircraft landings.

Hacked at the forum first for something similar but I don't think I know how to search. Tried searching for DATEIF and count days between dates but couldn't find a solution.

Thank you in advance for any help you can offer.
Rich Ard from Texas

 

[FDibbins]

Hard to make out what your data looks like - and what would some sample answers look like?

 

[Rich Ard]

Hard to make out what your data looks like - and what would some sample answers look like?

Hi Ford

I'm looking for patterns of usage. Are aircraft types used daily or 3 days on then 3 days off? Is it a random pattern of usage? This is why I'm interested in counting the days between landings. There's a longer story but I don't want to consume pages for an explanation. The short story is to eventually approach an airline with a business case. One revenue source for airports are landings. Hence my a interest in analyzing landings.

My data would look something like this. Equipment Code is an aircraft type.

Again, big thank you for your help.

Dynamic Table Report for nonstop Cargo (Air Only) landings for travel between October 1, 2016 and October 11, 2016

[TABLE="width: 530"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Equipment Code[/TD]
[TD]Equipment Code[/TD]
[TD]Equipment Code[/TD]
[TD]Equipment Code[/TD]
[/TR]
[TR]
[TD]Oct 1, 2016[/TD]
[TD]74M[/TD]
[TD]77F[/TD]
[TD]SU9[/TD]
[TD]PA2[/TD]
[/TR]
[TR]
[TD]Oct 2, 2016[/TD]
[TD]74F[/TD]
[TD]74M[/TD]
[TD]M1F[/TD]
[TD]124[/TD]
[/TR]
[TR]
[TD]Oct 3, 2016[/TD]
[TD]74M[/TD]
[TD]225[/TD]
[TD]PA2[/TD]
[TD]SU9[/TD]
[/TR]
[TR]
[TD]Oct 4, 2016[/TD]
[TD]74M[/TD]
[TD]77X[/TD]
[TD]M1F[/TD]
[TD]124[/TD]
[/TR]
[TR]
[TD]Oct 5, 2016[/TD]
[TD]74F[/TD]
[TD]74M[/TD]
[TD]225[/TD]
[TD]PA2[/TD]
[/TR]
[TR]
[TD]Oct 6, 2016[/TD]
[TD]74F[/TD]
[TD]ABY[/TD]
[TD]225[/TD]
[TD]YN2[/TD]
[/TR]
[TR]
[TD]Oct 7, 2016[/TD]
[TD]74F[/TD]
[TD]74M[/TD]
[TD]77X[/TD]
[TD]124[/TD]
[/TR]
[TR]
[TD]Oct 8, 2016[/TD]
[TD]74M[/TD]
[TD]74M[/TD]
[TD]77F[/TD]
[TD]YN2[/TD]
[/TR]
[TR]
[TD]Oct 9, 2016[/TD]
[TD]SU9[/TD]
[TD]T20[/TD]
[TD]YK2[/TD]
[TD]YN2[/TD]
[/TR]
[TR]
[TD]Oct 10, 2016[/TD]
[TD]PA2[/TD]
[TD]PAT[/TD]
[TD]YK4[/TD]
[TD]YN7[/TD]
[/TR]
[TR]
[TD]Oct 11, 2016[/TD]
[TD]73C[/TD]
[TD]73X[/TD]
[TD]A58[/TD]
[TD]74M[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]

 

[FDibbins]

So what would an expected answer/s look like?

 

[hiker95]

Rich Ard,

1. What is he worksheet name for the raw data in your reply #3?

2. What is the worksheet name where the results will be recorded?

3. Can we have a screenshot (manually formatted by you) of what the results should look like?

 

[Rich Ard]

1. Worksheet name is a generic name I get when I pull data from the database. In this case it is named Schedule Dynamic Table Report
2. I will call the worksheet for the results LandingsFrequency.
3. I'm having difficulty uploading a screen shot. I used the table feature here in Mr. Excel to create what I image the output would look like. Note some aircraft types come infrequently so all columns will not be filled equally as others.

I'm very grateful for your time. I look forward to learning something new. Thx

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]Equipment Code
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]124
[/TD]
[TD="align: center"]225
[/TD]
[TD="align: center"]73C
[/TD]
[TD="align: center"]73X
[/TD]
[TD="align: center"]74F
[/TD]
[TD="align: center"]74M
[/TD]
[/TR]
[TR]
[TD="align: center"]Days between landings
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]7
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]5
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]20
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

Rich Ard,

I have examined your raw data, and, results, and, can not see the logic to solve your request.

Here are screenshots (I think that the worksheet names are correct) that may help someone else to solve your request.

Excel 2007
	A	B	C	D	E	F
1	Date	Equipment Code	Equipment Code	Equipment Code	Equipment Code
2	Oct 1, 2016	74M	77F	SU9	PA2
3	Oct 2, 2016	74F	74M	M1F	124
4	Oct 3, 2016	74M	225	PA2	SU9
5	Oct 4, 2016	74M	77X	M1F	124
6	Oct 5, 2016	74F	74M	225	PA2
7	Oct 6, 2016	74F	ABY	225	YN2
8	Oct 7, 2016	74F	74M	77X	124
9	Oct 8, 2016	74M	74M	77F	YN2
10	Oct 9, 2016	SU9	T20	YK2	YN2
11	Oct 10, 2016	PA2	PAT	YK4	YN7
12	Oct 11, 2016	73C	73X	A58	74M
13
Schedule Dynamic Table Report

Excel 2007
	A	B	C	D	E	F	G	H
1		Equipment Code
2		124	225	73C	73X	74F	74M
3	Days between landings	1	3	1	1	3	7
4		5	1	20	1	3	5
5		2	3	75	1	3	2
6		5	1		1	3	20
7		1	3		1	3
8		5	1		1	3
9		2	3		1	3
10
LandingsFrequency

If the screenshots are NOT correct, then:

See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Rick Rothstein]

Rich Ard,

I have examined your raw data, and, results, and, can not see the logic to solve your request.

Like hiker95, I cannot see the logic to for the output table that you want given the data you showed us. Something for you to consider when clarifying this question for us as well as for any questions you may ask in the future...

One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your the data, its layout and the overall objective for it).

 

[mole999]

I'm not sure there is a pattern, but I think the OP knowing the FREQUENCY (MIN and MAX with MEAN) of a flight type would be helpful to his case. Whether its spotting the incoming drugs mules or being able to accommodate freight.

 

[Rich Ard]

Sorry for upsetting you Rick. And thanks to you andeveryone else in this forum. I learn a lot and I'm grateful to all thecontributors. And a big thank you to hicker95 for your consideration in trying to find a solution.

Problem statement:
Background
I work for an airport (public entity). Through our rates and charges we collectdata on landed aircraft. Landings is a source of revenue, hence we collectthe data.
Data collected includes but not limited to the type of aircraft that landed,and the date the aircraft landed. Aircraft type is identified by acode (e.g. and Antonov 225 is 225, a Boeing 777 freighter is 77F).


DataOutput
The data output I get is not ideal. It an old government system. In anattempt at humor I think it runs off Microsoft abacus (which was the version ofWindows before Window v1.0). Hopefully I made you laugh.

The data output is as follows:
*Column
The first column is the date the aircraft landed.
All other columns are types of aircraft by aircraft code.
*Row
The first row are headers
A1 is the date header for column 1
All other columns only say "Equipment Code" to indicate that thecolumn is populated by the types of aircraft that landed on that date.
*Closing
An example of a shortcoming of the data output is that aircraft 77F doesn'talways populate in the same column. Or aircraft T20 doesn't always populate inthe same column.

Goal
I want to analyze by type of aircraft the frequency each aircraft type lands.The aircraft I'm studying are freighter aircraft. Aircraft that carry aircargo. I am not studying passenger aircraft.
I am analyzing how air freighters are being utilized. I'm studying airfreighter utilization in the air logistics supply chain.
Over a long time series (several years) I want to know the number of daysbetween landings for each aircraft type.
*First pass questions I have in my analysis:
-What patterns are there, if any, in the utilization of aircraft types? Is theaircraft type used daily, every 3 days, used randomly? Why?
-If I review a long time series, are there seasonal patterns in the use ofaircraft? Why? Will I discover that for certain times of the year the aircraftis flowed into the Southern Hemisphere (where the seasons are opposite from theNorthern Hemisphere)?

Closing
This analysis is part of a larger study I'm doing. Even though I'vedeveloped relationships in the air logistics community, they are veryguarded in sharing information. Once I arm myself with facts, hopefully I canget individuals to open up and share more information with me.

The long view is to create a business case to convince a air freightercompany or an airline with air frieghters (Lufthansa, Air China, EVA, AtlasAir) to begin a new route from my city to someplace in the world.

END BACKGROUND EXPLINATION

Thank you in advance again to everyone. If a solution can't be found then I'll figure something out. It won't be efficient but I'll have to figure something out.

Rich Ard