Need to calculate flying hours

**nchat76** · 05-15-2005, 11:58 AM

I have a pilot friend who needs to calculate his flying hours. After every seven days, he needs to know if he has crossed the limit of 30 hours. So I have created an Excel sheet for him where he can enter the number of hours he has flown every day. How do I set a formula to add the hours for every seven days and then another formula that tells him the number of hours left for the 30-hour limit or whether that limit has been crossed.
There is a different limit for every 30 days as well, but once I know the 7-day calculation, I will be able to extrapolate that to calculate the 30-day figures.

**duane** · 05-15-2005, 12:17 PM

assuming the dates are entered in a1:a1000, and the hours in b1:b1000

put this in cell c1 and copy down

=SUMPRODUCT((A1-$A$1:$A$1000<8)*(A1-$A$1:$A$1000>=0)*($B$1:$B$1000))

it adds up all hours for which the date of the current row less other dates is <8 and >=0

you could then format the cell with condition format to highlight if the value is > 30

**olasa** · 05-15-2005, 12:32 PM

I just saw Duane had a solution...so this is an alternative:
=IF(WEEKDAY(A2,2)=1,30-B2,D1-B2)

Ola Sandström

Encl. Picture
Column C is Custom formatted: DDD to show the day of the week
Change all ; to , if that is your normal standard

**nchat76** · 05-15-2005, 01:45 PM

What format should the hours be entered in? The hours are not whole numbers every time. They could be 5 hours 35 minutes on one day, 6 hours 15 minutes on another.
I had entered them as 05:35:00, 06:15:00, etc. So when I put in the condition for 30 hours as >30, the formula is giving me vague answers and when I put the condition as 30:00:00 it returns an error.