Excel help
Thread Starter
Joined: Jul 2005
Posts: 264
Likes: 7
From: australia
Excel help
Help, my Excel skills are poor. I need a formula for a 90 day rolling instrument flight time, ie, after 90 days drop of first flight and add 90th etc. Can anyone help? Thanks in advance.
Joined: Aug 2007
Aviation Qualifications: ATPL
Posts: 57
Likes: 1
From: The World
Try this:
Assuming column A are the dates (ie. A1 to A123), and column B you flight/block times (B1 to B123), then in the cell you selected insert the following furmulae:
=SUMIF(A1:A123),">="&TODAY()-90,B1:B123).
OR
There is an other possibility based on NOW instead of TODAY, but this needs one extra column and goes as follows:
Assuming column A are the dates and column B the Blocks ON times, then column C will contain the sum of A + B in their respective rows (and formatted as dd/mmm/yyyy hh:mm) - this column could be hidden later on as it is only for calculation purpose. Column D would contain your flight/block times.
The formulae would then be:
=SUMIF(C1:Cxx),">="&NOW()-90,D1: Dxx)
For you to see.
Happy contrails
MP
Assuming column A are the dates (ie. A1 to A123), and column B you flight/block times (B1 to B123), then in the cell you selected insert the following furmulae:
=SUMIF(A1:A123),">="&TODAY()-90,B1:B123).
OR
There is an other possibility based on NOW instead of TODAY, but this needs one extra column and goes as follows:
Assuming column A are the dates and column B the Blocks ON times, then column C will contain the sum of A + B in their respective rows (and formatted as dd/mmm/yyyy hh:mm) - this column could be hidden later on as it is only for calculation purpose. Column D would contain your flight/block times.
The formulae would then be:
=SUMIF(C1:Cxx),">="&NOW()-90,D1: Dxx)
For you to see.
Happy contrails
MP
Joined: Aug 2000
Posts: 371
Likes: 0
From: (Not always) In front of my computer
Keep it simple
I have an excel spreadsheet where each row is a day. I log my instrument flight time in column L in decimal hours. eg. 1.2, 0.0, 2.3, 1.4, 0.0, 2.5
Column M is simply =SUM(L1:L89), =SUM(L2:L90), =SUM(L3:L91) etc.
Column M will read, 1.2, 1.2, 3.5, 4.9, 4.9, 7.4
There is no need to worry about a rolling total like last 7, 30, and 365 day flight time totals as there is no need to subtract times.
You only need to know how many hours you have logged in the last 90 days.
Works for me ...
Two Dogs
Column M is simply =SUM(L1:L89), =SUM(L2:L90), =SUM(L3:L91) etc.
Column M will read, 1.2, 1.2, 3.5, 4.9, 4.9, 7.4
There is no need to worry about a rolling total like last 7, 30, and 365 day flight time totals as there is no need to subtract times.
You only need to know how many hours you have logged in the last 90 days.
Works for me ...
Two Dogs
