How to calculate the data in excel 2002 including only the last 9.

[Guest]

I'm a pilot trying to use excel 2002 to calculate my logbook, and need to
know how much of the time was in the last 90 days, I'm looking for a formula
that will help me sort, and add the data from just the last 90 days.

 

[Frank Kabel]

Hi
maybe something like
=SUMIF(A1:A1000,">=" & TODAY()-90,B1:B1000)

 

[Ken Wright]

Have you considered using a Pivot table? Assuming your data is in a database
style format, then by adding another field to it that quesries the date for that
record and based on whether it is within the last 90 days or so you will be able
to filter on that alone and yet use all the power that pivot Analysis gives you.
Assuming your data has dates in Col A, then create another field and start with
=A2>=(TODAY()-90) and copy down. This will give you TRUE for all records that
are within the last 90 days and this can be used as a filter in the Pivot table.

Some introductory links for Pivot tables

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Ragdyer]

How much of what time ... flight time?
How is your log configured?
Do single flight duration numbers (times) span multiple dates (rows)?

Post back with column and row breakdowns and explanations of your logging
procedures.

 

[Guest]

It appears that Frank Kabel's approach works for what I want, but if you know
a better way I am still willing to give it a try.

The times are my flight times and the breakdowns of what time of time it
counted for.

My log is set up with Column A: date, Column B: N #, Column C: The type of
aircraft, Column D:The origin of the flight, Column E: stops on the way,
Column F: the destination, Column G: the total time, Column H: Single engine
land time, Column I: Multi engine land time, Column J: # of landings, Column
K: Night time, Column L: Actual IFR time, Column M: Hood time, Column N: # of
approaches, Column O: sim time, Column P: Cross country time, Column Q: solo
time, Column R: PIC time, Column S: dual time. Each row consist of the date
and times for only one flight, if there is more than one flight in a day I
log each flight seperatly. A flight is logged with only one date.

 

[Guest]

Thanks Frank that function appears to work for me.

 

[Guest]

Thanks for the info Ken, I'm sure it will come in handy some day, but I don't
think it would work in this situation, because my data has too many open
cells, if I don't have a particular type of time I leave the cell empty. It
looked like all the cells needed to be filled in for the pivot table to work.