Flight Time Extraction for 24 hrs, Weekday, and Month blocks

[flydude]

I am trying to extract the flight time from a depart date, depart time, arrival date, and arrival time.

First I would like to gather all minutes of flight time flown from 00:00-01:00 and so on.
Second, I would like to gather all of the flight time flown on Mondays and so on.
Third, I would like to gather all flight time flown in January and so on.

The below is a snapshot of my data, but I would like to email the snapshot of the actual spreadsheet if someone can help.


[TABLE="width: 3133"]
<colgroup><col span="2"><col span="2"><col span="24"><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Leg Depart Date[/TD]
[TD]Leg Depart Time[/TD]
[TD]Arrival Date[/TD]
[TD]Arrival Time[/TD]
[TD]00:00 - 01:00[/TD]
[TD]01:00 - 02:00[/TD]
[TD]02:00 - 03:00[/TD]
[TD]03:00 - 04:00[/TD]
[TD]04:00 - 05:00[/TD]
[TD]05:00 - 06:00[/TD]
[TD]06:00 - 07:00[/TD]
[TD]07:00 - 08:00[/TD]
[TD]08:00 - 09:00[/TD]
[TD]09:00 - 10:00[/TD]
[TD]10:00 - 11:00[/TD]
[TD]11:00 - 12:00[/TD]
[TD]12:00 - 13:00[/TD]
[TD]13:00 - 14:00[/TD]
[TD]14:00 - 15:00[/TD]
[TD]15:00 - 16:00[/TD]
[TD]16:00 - 17:00[/TD]
[TD]17:00 - 18:00[/TD]
[TD]18:00 - 19:00[/TD]
[TD]19:00 - 20:00[/TD]
[TD]20:00 - 21:00[/TD]
[TD]21:00 - 22:00[/TD]
[TD]22:00 - 23:00[/TD]
[TD]23:00 - 24:00[/TD]
[TD] [/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD] [/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD="align: right"]Jan 7, 2005[/TD]
[TD="align: right"]12:54:00 AM[/TD]
[TD="align: right"]Jan 7, 2005[/TD]
[TD="align: right"]3:00:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Apr 28, 2005[/TD]
[TD="align: right"]2:23:00 AM[/TD]
[TD="align: right"]Apr 28, 2005[/TD]
[TD="align: right"]4:20:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jun 30, 2005[/TD]
[TD="align: right"]1:55:00 AM[/TD]
[TD="align: right"]Jun 30, 2005[/TD]
[TD="align: right"]3:33:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 3792"]
<colgroup><col><col><col span="2"><col><col><col><col span="2"><col span="2"><col span="24"><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col span="2"><col><col><col></colgroup><tbody></tbody>[/TABLE]


Ultimately, I am trying to get total minutes flown in these different strata so that I can get mishap rates per strata.

 

[lrobbo314]

If you create pivot tables with your data you can group different time periods to display the info in the ways you're describing.
Create a pivot table, put the date in the row area and your departure and arrival times in the columns area. Then right click on the dates info and click group to bring up the different grouping options.

 

[flydude]

Thanks for the response, but i don't know if that's exactly what I'm looking for. I will give it a try but was looking for a formula of some sort that would look at the depart and arrival times and give me the exact minutes that were flown for that hour block. ..also some type of formula would take into count the depart date, along with the depart/arrival times and give me total minutes or hours flown during monday. ..and the again for all the months. Ultimately I would like to record a macro to apply this to multiple sheets. I will try method just to make sure it is not what I want though. Thanks