Excel - Rolling 28 day logbook question
 

Hi guys,

Requiring a little help from those Excel boffs amongst us....

I'm using my own created Excel document. It works fine but I'm trying to refine it a little. I've a basic - intermediate knowledge of Excel.

My logbook has a number of columns including aircraft, date, departure, arrival, block times. I'd like to try and keep on top of my overtime which is triggered when my total block hours over a 28 day period exceeds 74 hours.

I've found part of a possible solution but drawn a blank from being able to proceed further. So far I've used the TODAY() function and TODAY()-28 to give me a 28 day range.

I know the VLOOKUP function but can fathom out how to use the TODAY & TODAY-28 to be applied in this query to get the cumulative total block hours over this range.

If that wasn't complex enough, I would also like to draw a graph over the last 3 months to show a running 28 day block hour total for any given day over a 3a month period.

What advise can anyone lend on this subject? Better still are there any sample websheets that I can view which has this feature?

Thanks for any info

Without invoking VBA, I'd be looking at Conditional Formatting, Index and Match functions. Really need to see a sample of what you are trying to do.

Using Office 2010 gives shedloads of CF options.

A combination of something below might work. Not an expert, but use these to track last date of an event and how much done on a particular day.

=INDEX($A$13:$A$377,MATCH(9.99999999999999E+307,B13:B377))

=LOOKUP(9.99999999999999E+307,B13:B377)

Date down the left, numbers in the next column. columns to the right can be added as required.

You can section the SS into date areas and build a graph(s) from there. High maintenance, really, when you could buy a prog from T'internet for a few spondoolicks.

Conditional Formatting, combined with a separate, linked, area on your SS to highlight bit and bobs, with a graph based on that, may be your easiest answer, though.

PS. It's after midnight, might not make sense:O

The function, DSUM, is the answer. I did a flying hours currency thing 7 years ago as a mess about. It does rolling hours and an 'Are you Current' type answer. Absolute mess to look at, and it will take some fettling to sort. Just need to sort out what it was I was doing........

All done with formula; no VBA.

I have no idea if this what you are looking for, but if I understand your request, this works for me:

In A1...Ax: the date of your flight
In B1...Bx: the time your flight ended (Blocks ON, or as per your needs)
In C1...Cx type: =A1 +B1 (format this cell as dd-mmm-yy or similar mmm-dd-yy)
In the cell you need the answer, type: =SUMIF(C1:Cx,”>=”TODAY()-28,C1:Cx)

This will give you the flight time (or whatever your requirement) over the last 28 days (or whatever time lapse you decide)

Regarding your second question (graph), I never looked at it. nevertheless, you could dedicate a page (tab) (hiden at a later stage, dedicated to some calculations replicating the above formulae (with adjusted time lapse) and laid out into a table from which your could extract a graph displayed on a visible tab.
A bit of work and may be not very aesthetic but it might reach your goal.

MP