excel logbooks

[LDG_GEAR _MONITOR]

i'm sure someone can answer this simple one (but not simple for me)

i have created an excel database logbook but carnt work out an equasion to add up how many hours done in the last 28/90/365 days.

The database is the same as the paper format ie colums go date (day/month/year),reg,destination ,etc, dep time,arrival time, P1 time single day, P1 time single night et etc

What calculation do i need to check the first column to find any date between today and 28 days previous - if the date falls in that range then to add up the time in the column for P1 time etc (if that makes sense !)

or if there is an excel guru out there i can forward the file to get it sorted !

thanks in advance

[737TG]

It is simple.

Use the formula "=sum(a1:a28)" to find the total for a series of 28 days and use the FILL DOWN function (CTRL D) or drag the cell down by the "HANDLE" to automatically continue your 28 day calculation forever!

It makes life much easier if your data includes ALL dates (even those with no flying)...

:cool:

[LDG_GEAR _MONITOR]

that would work but i only record days i fly(not every day) and some days have upto 15 entries so that idea doesnt work unfortunatly

[planecrazy.eu]

I have got a mental blank and cant thing how the code goes and i am no near excell at the moment but the way would be to preform a lookup, vlookup i think, and set it to look up all records "after" 28 days ago. You will have to calculate 28 days or you can just manually enter it. To auto calc 28 days it will have to be =now(sum-28) i think thats it any way, i will have a look later if you have not solved it by then...

You need to get it to show records after a given date, the given date would be 28 days, i am sure it can be done without macros too.

[Beagle-eye]

Edited because I didn't read the original post correctly and provided a load of unasked for information :-(

[potkettleblack]

I am not very good at the more complicated formulae available in excel so I tend to get around this by using IF statements. In your example you could set up to the far right of where your data is a series of IF statements in as many columns as you required. The first IF statement could be =IF(A2=now(sum-28),E2,0). Assuming in cell A2 you had a date and this date was less than 28 days then it would return the value in cell E2 - assuming your flight hours of course were in cell E2. You would copy this formula all the way down the rows you are using and then sum it at the bottom to give the number of hours flown in the last 28 days. For 90 days, 150 days or whatever else you need just put them in the next column using the same sort of formula as above but replacing the 28 with 90 etc.

Another good practice is to do a checksum somewhere on your spreadsheet (which should add to zero) to make sure that the sums of all the IF statements add up to your total hours flown. You can also hide all of the columns containing the formulas if you don't want to see them and just have a nice summary area somewhere on your spreadsheet which has references to the totals.

Hopefully you can follow what I meant. I am sure there are easier ways to do it but like I say I find IF statements a lot easier to deal with and to review.

[Keef]

Mine does it that way. There are columns off the right hand end of the page, set to zero width, with "IF" statements comparing the date in column 1 with the date now, and showing the hours from column 10 if the difference <31 or <91. Those columns are totalled, and the total appears in the box at the bottom of the logbook.

The same principle counts landings (just in case), Instrument approaches, and suchlike.

It's not elegant, but I understand it and it works.

[Tosh McCaber]

For a payment of £32-33 for a thre year licence, you can buy Safelog, a computer spreadsheet laid out in the same format as the regular JAA logbook- no glitches, very straightforward, and, best of all, tested and tried. I can vouch for it.

Available from:

http://www.dauntless-soft.com/PRODUCTS/Safelog/order.asp

[zoink]

ok.. this was bothering me for a while

what you need is the following

1) A cell with today's date with the same formatting as the dates you place on each flight record - For this I will refer to this as cell C1

For the cell to display the last say 28 days of hours use the following

=SUMIF(B1:B20,">="&C1-28, A1:A20)

where A1:A20 are 20 of the flight log hours - P1 in your logbook thingy
B1:B20 are 20 of the flight log dates

so to change it for the 90 just change to

=SUMIF(B1:B20,">="&C1-90, A1:A20)

and 365
=SUMIF(B1:B20,">="&C1-365, A1:A20)


In english the formula is

Add up all the flight hours for each flight record which is newer than today less XX days

let me know if that works

I've tested in on Open Office Calc but it should do for excel

[LDG_GEAR _MONITOR]

zoink

thanks for that - just off aviatin but as soon as I get 5 mins i will give that a try and let you know how it goes !