Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
TylerMaricich
 
Posts: n/a
Default How to calculate the data in excel 2002 including only the last 9.

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.
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany


TylerMaricich wrote:
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.

  #3   Report Post  
Ken Wright
 
Posts: n/a
Default

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" wrote in message
...
Hi
maybe something like
=SUMIF(A1:A1000,"=" & TODAY()-90,B1:B1000)

--
Regards
Frank Kabel
Frankfurt, Germany


TylerMaricich wrote:
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004


  #4   Report Post  
Ragdyer
 
Posts: n/a
Default

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"TylerMaricich" wrote in message
...
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.


  #5   Report Post  
Tyler
 
Posts: n/a
Default

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.

"Ragdyer" wrote:

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"TylerMaricich" wrote in message
...
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.





  #6   Report Post  
Tyler
 
Posts: n/a
Default

Thanks Frank that function appears to work for me.

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany


TylerMaricich wrote:
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.


  #7   Report Post  
Tyler
 
Posts: n/a
Default

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.

"Ken Wright" wrote:

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" wrote in message
...
Hi
maybe something like
=SUMIF(A1:A1000,"=" & TODAY()-90,B1:B1000)

--
Regards
Frank Kabel
Frankfurt, Germany


TylerMaricich wrote:
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
sharing/using/saving Excel 2002 files in Excel 2003 maze2009 Excel Discussion (Misc queries) 0 January 20th 05 08:27 PM
Fetching External Data from Excel Sri Excel Discussion (Misc queries) 2 January 3rd 05 12:46 PM
How do I display a data table in an Excel 2002 chart? Dr. Mark F. Charts and Charting in Excel 3 December 29th 04 04:04 PM
Data Forms in Excel 2002 kez Setting up and Configuration of Excel 1 December 8th 04 11:17 PM


All times are GMT +1. The time now is 10:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"