Thanks:  0
Likes:  0

1. I am looking for a way to create a table whereby I enter a date and have the function look at the column next to that date (with a value for hours) and sum the hours for that date plus the six previous dates.

Like this:

Date Hours
4/1 5.0
4/2 4.5
4/3 7.5
4/4 9.0
4/5 10.5
4/6 0.0
4/7 6.0
4/8 5.5
4/9 4.0

I would enter the date (4/8) and it would calculate the sum of the hours for 4/8 backward 7 days. If I change the date, it would recalculate back the correct number of days no matter what date I entered. I also need to set up for the previous 30 days, 90 days and 365 days.

Anyone have any ideas?

Thanks

2. On 2002-04-28 20:41, btolfree wrote:
I am looking for a way to create a table whereby I enter a date and have the function look at the column next to that date (with a value for hours) and sum the hours for that date plus the six previous dates.

Like this:

Date Hours
4/1 5.0
4/2 4.5
4/3 7.5
4/4 9.0
4/5 10.5
4/6 0.0
4/7 6.0
4/8 5.5
4/9 4.0

I would enter the date (4/8) and it would calculate the sum of the hours for 4/8 backward 7 days. If I change the date, it would recalculate back the correct number of days no matter what date I entered. I also need to set up for the previous 30 days, 90 days and 365 days.

Anyone have any ideas?

Thanks
Assume your sample data is in A1:B10 including labels.

In E1 enter: 7 [ your number of days criterion ]
In D2 enter: 4/8 [ your criterion date ]

In E2 enter:

=SUM(OFFSET(\$B\$2,MATCH(D2,A:A,0)-E1-1,0,E1,1))

Note. The formula does not check whether there are at least E1 days in the data area.

3. Hi.
...perhaps this can be accomplished with several lookup and reference functions(which I know very little about)
If you'd like, E-mail the sheet to TsTom@Hotmail.Com along with any other details that you feel would help me help you.
Tom

Now all I have to do is figure out how that formula does what it does...
Tom

5. On 2002-04-28 21:03, TsTom wrote:
Now all I have to do is figure out how that formula does what it does...
Tom
If stuck, just call me in.

6. Thanks!! It works great. Now maybe I should figure out a way to check to make sure there is data in those cells. This program is to calculate pilot flying time to ensure I am under the maximum number of flying hours for a 7 day, 30 day, 90 day and 365 day period. I can enter my flight hours and have it keep track. Then when I need to check my previous time period, I can plug in the end date and the number of days in the past to check and verify my hours.

Thanks again

7. On 2002-04-28 21:16, btolfree wrote:
Thanks!! It works great. Now maybe I should figure out a way to check to make sure there is data in those cells. This program is to calculate pilot flying time to ensure I am under the maximum number of flying hours for a 7 day, 30 day, 90 day and 365 day period. I can enter my flight hours and have it keep track. Then when I need to check my previous time period, I can plug in the end date and the number of days in the past to check and verify my hours.

Thanks again
If you know what to do in case the data is insufficient wrt the days criterion, it is not that hard to incorporate it in the OFFSET formula.

8. Now that this appears to work so well, I am thinking of a way to automatically calculate the time periods and have it update itself on a daily basis. So the date field would automatically increment itself and the new calculation would then adjust the flight times automatically. Is this possible?

OFFSET(\$B\$2,9-E1-1,0,E1,1)

9, in the above is the value returned by the nested match function...

The answer is correct for the overall formula, and therefor, in my case, it must be returning this Range B3:B9

How is the Offset function returning B3:B9 ?
E1 = 7
9-7-1 = 1

Thanks for the help...
Tom

10. On 2002-04-28 21:25, btolfree wrote:
Now that this appears to work so well, I am thinking of a way to automatically calculate the time periods and have it update itself on a daily basis. So the date field would automatically increment itself and the new calculation would then adjust the flight times automatically. Is this possible?
Do you mean by time periods what goes in E1?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•