Microsoft Excel and E-Logbook

[+TSRA]

Hi all,

Im quite proud of myself in my abilities at programming but have come up against an unexpected problem with my self made logbook in excel.

I want to be able to pull from my data the last 30, 90, 180 and 360 days worth of flight times. I would have thought this would have involved a simple sumif formula, but it always comes back #ref or invalid

for example I have tried

=sumif('logbook'a6:A1400,"",g6:r1400)

where A column equals the date of the flight and
G through R equal all the flight columns (Day, Night, PIC, SIC, etc)

and other variations on the SUM, SUMIF, IF etc formulae.

Ive had a friend who was able to do it all with a macro but it is not able to self-up-date with each new flight, making it useless as a record keeping device (considering I would have to re-write to code for each flight, assuming I understood Macro code...)

Im hoping someout out there has overcome this problem and could steer me in the right direction. For me this is a last resort asking, but after 10 months of trying I figure its time to be a man and ask for help...

but dont let my wife know...

Sorry about the prev. edition of this post!!!!

[Keef]

I nearly drove myself silly trying to do the same thing. It ought to be easy, but it isn't.

In the end, I added some columns, over on the far right hand side of the spreadsheet. They are 30 days, 60 days, etc, and follow the general principle =IF(A3>(TODAY()-30),B3,0)
Column A is date, Column B is hours and decimal.

Then I just add the relevant columns. The totals copy over to the "summary" detail bit of the logbook.

It's ugly, and inelegant, and it works.

[rotorcraig]

It's ugly, and inelegant, and it works

It's what I'd do too. The only other way I can think of is to write a Visual Basic Macro that programatically scans through the logbook and does the calculations, and is called with one elegant statement =flighttime(30) etc. Lots more effort to come up with the same number.

I'd say do what Keef says and hide the inelegant columns

RC

[Temet_Nosce]

Keep things really simple!

Setup another sheet with every day of the year on the left column and use the VLOOKUP function to extract totals for that day from your logbook. Then running totals are as simple as =sum(B1:B31). Drag this formula down and it becomes =sum(B2:B32) etc.

TN

[Telstar]

There are commercially available electronic logbooks out there, tons and tons of functions, can track everything under the sun, JAR compliant and cost not a great deal of money.

Or is that not as much fun as building your own?

[+TSRA]

Keef, rotorcraig and temet_nosce - Thank you! I played around with all three ideas and have it working better than I actually wanted it to, so thanks again.

Telstar - Ive tried one store bought logbook and did not like it at all, which is why I decided to program my own, that and I dont need it to conform to JAR, FAA, TC regs. I also wanted the challenge of writitng all the formulas myself and seeing the end result. After all, pilots all cant just be pretty faces in the crowd

[MetoPower]

TS,

Could you help and let us know how you've set up the formulae

Thanks