Counting blocks

[shorn]

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.

	a	b	c	d
1	Time	Aircraft #1	aircraft #2	Aircraft #3
2	1:00	Mx Flight	Janes flight	Mx flight
3	1:30
4	2:00		Mx flight	Mikes flight
5	2:30
6	3:00	Bobs flight
7	3:30		Joes flight
8	4:00			Marrys flight
9	4:30	Mx flight
10	5:00
11	5:30
12	6:00	End of day	End of day	End 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

 

[MikeWx]

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
	A	B	C	D
1	Time	Aircraft #1	aircraft #2	Aircraft #3
2	1:00	Mx Flight	Janes flight	Mx flight
3	1:30	*	*	*
4	2:00	*	Mx flight	Mikes flight
5	2:30	*	*	*
6	3:00	Bobs flight	*	*
7	3:30	*	Joes flight	*
8	4:00	*	*	Marrys flight
9	4:30	Mx flight	*	*
10	5:00	*	*	*
11	5:30	*	*	*
12	6:00	End of day	End of day	End of day
13	*	*	*	*
14	Mx Flights	3.5	1.5	1
15	Available	1.5	3.5	4
Sheet1

 

[shorn]

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]

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
1	12:00	MXtime (grey)
2	12:30	grey
3	1:00	grey
4	1:30
5	2:00
6	2:30	MXtime (grey)
7	3:00	Grey
8	3:30	John (grey)
9	4:00	grey

<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