Counting blocks

shorn

New Member
Joined
Sep 12, 2013
Messages
40
I have a schedule program that I use to gather data from which lists whether or not a person is listed to fly an aircraft at a time or if they have been dispatched. If they are scheduled for a person or maintenance they can be one of several colors but if they are dispatched they are always the same color. My excel sheet currently loads the downloaded schedule into an area and then counts the colors in each cell just fine, but recently we ran into a small issue.

See we count a dispatched aircraft as being available to customers if they are dispatched. The problem is that we don't want to count if it was a maintenance flight as it was not available to customers.

What i'm tying to find is a way that we can tell if a block was a maintenance flight (MX flight) and subtract it from our total available time. A block is from one name to another..so b2:b5 is a block of 2 hours.

abcd
1TimeAircraft #1aircraft #2Aircraft #3
21:00Mx FlightJanes flightMx flight
31:30
42:00Mx flightMikes flight
52:30
63:00Bobs flight
73:30Joes flight
84:00Marrys flight
94:30Mx flight
105:00
115:30
126:00End of dayEnd of dayEnd of day

<tbody>
</tbody>

For the example assume that the colors of all the blocks from B2:d11 are the same

So Aircraft one had 3.5 hours of mx flights and was available for 1.5 hours
Aircraft 2 had 1.5 hours of mx flight and was available for 3.5
Aircraft 3 had 1 hour of mx flight and was available for 4 hours

The system needs to work across many columns as the fleet of aircraft changes and it needs to happen automatically when the data is loaded.

Any help you guys can give would be great! Thanks!

-Shawn
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

MikeWx

Active Member
Joined
Jan 16, 2010
Messages
436
Here is a brute force way to calculate the Mx Flight time which works for Mx Flights up to and including 2.5 hours. It has enough IF statements that you need Excel 2007 or later. The Available Time assumes each aircraft is available for 5 hours each day. Copy B14 and B15 across for each aircraft.

Excel Workbook
ABCD
1TimeAircraft #1aircraft #2Aircraft #3
21:00Mx FlightJanes flightMx flight
31:30***
42:00*Mx flightMikes flight
52:30***
63:00Bobs flight**
73:30*Joes flight*
84:00**Marrys flight
94:30Mx flight**
105:00***
115:30***
126:00End of dayEnd of dayEnd of day
13****
14Mx Flights3.51.51
15Available1.53.54
Sheet1
 

shorn

New Member
Joined
Sep 12, 2013
Messages
40
MikeWx,

Thanks for the reply! Unfortunately the time is a 24 hour period and i think the code there will become to large for even 2007...Im thinking the best solution would be using VB function which took in a specific array range at the top of every row. Any help would be swell though as my VB skills are not quite up to that level

-Shawn
 

shorn

New Member
Joined
Sep 12, 2013
Messages
40
MikeWx,

Thanks for the reply! Unfortunately the time is a 24 hour period and i think the code there will become to large for even 2007...Im thinking the best solution would be using VB function which took in a specific array range at the top of every row. Any help would be swell though as my VB skills are not quite up to that level

-Shawn

One thing that also has to be accounted for is that some times the mx block (which is a particular color) is followed by nothing. So the code needs to search for mx flight and count the number of spaces before it hits another block (defined by different name or end of mx flight color).

a
112:00MXtime (grey)
212:30grey
31:00grey
41:30
52:00
62:30MXtime (grey)
73:00Grey
83:30John (grey)
94:00grey

<tbody>
</tbody>

For instance the mx blocks in column A is A1:a3 and A6:a7 for a total of 2.5 hours of mx time.

Thanks for the help by the way..even if it is just pointing me in the right direction :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,601
Messages
5,597,117
Members
414,126
Latest member
jellevansoelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top