Flying-Hours calculation

[Corton Flyer]

One of my administrative duties is to keep a record of all of the flying-hours completed by a group of twenty pilots. I've constructed a spreadsheet and entered all of their flying records into it.

At the head of each column I have the date, aircraft type, registration number, pilot name, co-pilot name, other crew name, day flying, night flying, solo, dual, total captain hours, etc.

Whilst that I've completed the easy part of this project and that I can transfer each individual pilots flying-hours into his own seperate logbook (by filtering and copy/pasting into another worksheet), there are three other reports that I'm required to provide:

1. To be able to list the number of flying-hours completed during the previous 7 days (for each individual pilot).

2. To be able to list the number of flying-hours completed during the previous 30 days (for each individual pilot).

3. To be able to list the number of flying-hours completed during the previous 90 days (for each individual pilot).

I can't say that I'm an Excell guru; hence, I'd appreciate any assistance/help that anyone might be able to offer in my trying to resolve the above issues?

Many thanks

John

 

[Norie]

John

I think the first thing you should do is reconsider how the spreadsheet is structured.

I've only briefly read your post but from what I can gather the current structure doesn't seem to be conducive to easily analysing/manipulating the data.

Mind you it would perhaps help if you could post some sample data, because I could be totally wrong.

 

[edvwvw]

Hi Corton Flyer welcome to the board.

I have assumed that the hours are in column B

=SUM(INDEX(B:B,MATCH(99^99,B:B)):INDEX(B:B,MATCH(99^99,B:B)-6))

The figure at the end controls the number of days in the case illustrated this will add the last 7 days - if you want the last 30 change the -6 to -29.

Don't forget to format the result cell as [hh]:mm otherwise you will get an odd result

edvwvw

 

[Corton Flyer]

edvwvw

Thanks for your efforts. The hours were in fact in column P, so I changed the formula to reflect that column. The ensuing result didn't make any sense (the hours were formatted correctly).

Much scratching of head!

Cheers

John

 

[scottylad2]

Excel Workbook
	H	I	J	K	L	M	N	O
11				Last 7 Days		last 30 Days
12	pilot 3	01/12/2009	5:25	pilot 1	3:03	pilot 1	17:24	15/01/2010
13	pilot 4	02/12/2009	5:25	pilot 2	11:49	pilot 2	45:05	23/12/2009
14	pilot 1	03/12/2009	6:11	pilot 3	5:58	pilot 3	20:08
15	pilot 5	04/12/2009	1:10	pilot 4	13:33	pilot 4	32:53
16	pilot 5	05/12/2009	5:33	pilot 5	0:00	pilot 5	34:22
17	pilot 2	06/12/2009	3:29
18	pilot 3	07/12/2009	1:39
19	pilot 2	08/12/2009	2:40
20	pilot 5	09/12/2009	6:47
21	pilot 2	10/12/2009	4:39
22	pilot 2	11/12/2009	4:22
23	pilot 2	12/12/2009	6:08
24	pilot 4	13/12/2009	3:57
25	pilot 1	14/12/2009	6:40
26	pilot 4	15/12/2009	5:10
27	pilot 5	16/12/2009	2:22
28	pilot 5	17/12/2009	5:51
29	pilot 3	18/12/2009	6:38
30	pilot 4	19/12/2009	5:14
31	pilot 4	20/12/2009	5:44
32	pilot 5	21/12/2009	4:28
33	pilot 1	22/12/2009	7:08
34	pilot 1	23/12/2009	6:48
35	pilot 4	24/12/2009	5:15
36	pilot 2	25/12/2009	4:37
37	pilot 5	26/12/2009	3:53
38	pilot 3	27/12/2009	1:07
39	pilot 2	28/12/2009	7:19
40	pilot 3	29/12/2009	5:14
41	pilot 4	30/12/2009	2:25
42	pilot 2	31/12/2009	3:30
43	pilot 5	01/01/2010	7:00
44	pilot 2	02/01/2010	6:58
45	pilot 4	03/01/2010	2:25
46	pilot 5	04/01/2010	3:28
47	pilot 2	05/01/2010	7:21
48	pilot 2	06/01/2010	3:31
49	pilot 5	07/01/2010	3:30
50	pilot 3	08/01/2010	7:49
51	pilot 1	09/01/2010	7:58
52	pilot 5	10/01/2010	7:38
53	pilot 1	11/01/2010	6:23
54	pilot 5	12/01/2010	3:57
55	pilot 4	13/01/2010	3:52
56	pilot 4	14/01/2010	5:23
57	pilot 5	15/01/2010	4:56
58	pilot 4	16/01/2010	3:04
59	pilot 1	17/01/2010	3:03
60	pilot 3	18/01/2010	5:58
61	pilot 4	19/01/2010	4:16
62	pilot 2	20/01/2010	5:22
63	pilot 2	21/01/2010	6:27
64	pilot 4	22/01/2010	6:13
65			263:20
Sheet1

Excel Workbook
	N
12	17:24
Sheet1

 

[scottylad2]

The formulas in O are =Today()-7 and =Today()-30 so sumproduct will always show the last 7 or 30 days. If you need that to stay as a result each day, and not update the following date you could have some of the VBA guys write something up that will copy each days results as a value only result into another worksheet

 

[Corton Flyer]

scottylad2

Thanks for the info. I'll see how I can rearrange my workbook and give it a flight test.

Cheers

John

 

[Corton Flyer]

scottylad2

Thanks a million... having made a few minor changes to my workbook; it all works a treat!

Cheers

John

 

[scottylad2]

glad its something you can use

 

[Corton Flyer]

scottylad2,

How would I modify your previous formula to include an inserted column in say, column 'I'? This additional column shows the flying-hours of the co-pilot, and, with the way in which my workwook is laid-out, does indeed show the hours of pilot 2 (and others) within both columns. Hence, the single column only reflects those figures within that column. I guess what I'm trying to say is, that a pilot in the first column will also feature within the next column when he's flying as a co-pilot on a different date; hence, I need some method of picking-up those hours too!

Having manipulated your formula into my own workbook, it doesn't reflect those hours which are listed within the adjacent column; so it doesn't tell the whole story.

I hope I've made sense?

Cheers

John