Go Back  PPRuNe Forums > Misc. Forums > Computer/Internet Issues & Troubleshooting
Reload this Page >

Excel - Rolling 28 day logbook question

Computer/Internet Issues & Troubleshooting Anyone with questions about the terribly complex world of computers or the internet should try here. NOT FOR REPORTING ISSUES WITH PPRuNe FORUMS! Please use the subforum "PPRuNe Problems or Queries."

Excel - Rolling 28 day logbook question

Old 15th Apr 2011, 22:09
  #1 (permalink)  
Thread Starter
 
Join Date: Apr 2000
Posts: 83
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
take_that is offline  
Old 15th Apr 2011, 23:26
  #2 (permalink)  
 
Join Date: Apr 2005
Location: Earth
Posts: 366
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
Spurlash2 is offline  
Old 16th Apr 2011, 00:08
  #3 (permalink)  
 
Join Date: Apr 2005
Location: Earth
Posts: 366
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.
Spurlash2 is offline  
Old 22nd Apr 2011, 06:25
  #4 (permalink)  
 
Join Date: Aug 2007
Location: The World
Posts: 52
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
MetoPower is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Contact Us Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright © 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.