Any Excel wizards in the house??

[C-GPFG]

I'm trying to automatically add up flying hours for the past 7, 30, 90, 365 days in my excel logbook.

Date is column A, times are entered in columns G to P.

I think the formula should look like this....

=SUMIF($A$1:$A$9999,"<="&TODAY()-7,$G:P$1:$G:P$9999)

..but obviously it's not working.

Anyone see a mistake or have a different formula that works??

Thanks.

[snoopy]

Do you have an email you could pm me? I have a blank flight duty .xls file that you can have - it has all the formulas you need in it... and is adjusted for all the Ops Specs for extended duty/flight. The only thing you have to do is a manual re-set after 5 days off.

I tried to upload it but apparently .xls files are forbidden.

Cheers,
Kirsten B.

[Ogee]

I'm trying to automatically add up flying hours for the past 7, 30, 90, 365 days in my excel logbook.

Date is column A, times are entered in columns G to P.

I think the formula should look like this....

=SUMIF($A$1:$A$9999,"<="&TODAY()-7,$G:P$1:$G:P$9999)

..but obviously it's not working.

Anyone see a mistake or have a different formula that works??

Thanks.

What times are entered in columns G to P? What's in columns B to F? Anyways, that looks very overly complicated. I think you need Col 0 to be Last 7 Days, Col N to be last 30 days etc, then just put in a rolling sum function with the first cell made absolute until you get by a full 7 days, 30 days etc, Copy the formula down as you go. When you have the full days for each column, go in and remove the absolute reference to the top cell and make it all relative. In order to do this, you have to have every date entered, not just the dates you fly. Admittedly, that gives you a long spreadsheet, but its easy to set up.

I see your point trying to do it the other way, but without seeing your spreadsheet I doubt I could write the code. I can see that you don't want those quotes around the greater than or equal to sign;, you probably don't want the $signs in the True section, and you may need a comma before the last parenthesis. I dodn't know there was an &TODAY function.

[Louis]

The problem with a "rolling sum" function where the added cells range changes continuously is that you can't skip dates.

I had mine done like this:
SUMIF($A$10:$A814;">" & $A814-7;$C$10:$C814)

My dates were in column A starting from cell A10, the other end of array was left relative. Flight times in column C.
I don't remember why I had to leave the end of the arrays relative...

The formula you posted probably has trouble with the P to G column array. You would need either an array formula, or added SUMIFs, or another column with line totals.

[C-GPFG]

Ogee: It's set up like a standard logbook..so those columns are my flight times broken down into SE ME, day/night, PIC/SIC etc.. it's not purely a flight time monitor, rather I'm trying to have my logbook monitor my flight time automatically.

Louis: thanks..I was hoping to avoid adding a bunch of sumif's, but it looks like that's how it will have to be.


Was hoping to write a formula that was basically, today's date less 7, 30 days etc.. and sum the columns G to P in that date range.

[Ogee]

Ogee: It's set up like a standard logbook..so those columns are my flight times broken down into SE ME, day/night, PIC/SIC etc.. it's not purely a flight time monitor, rather I'm trying to have my logbook monitor my flight time automatically.

Louis: thanks..I was hoping to avoid adding a bunch of sumif's, but it looks like that's how it will have to be.


Was hoping to write a formula that was basically, today's date less 7, 30 days etc.. and sum the columns G to P in that date range.

Pm me and I'll give you an email that you can send your spreadsheet file to and I'll see if I can cook something up for you.

[tca]

i know it may be long taken care of, but this is how I went about it:
(last 30 days)

Code: Select all

=SUMIF(Logbook!$A$3:$A$3285,">="&(TODAY()-30),Logbook!H$3:H$3286)

(this calendar month)

Code: Select all

=SUMIF(Logbook!$A$3:$A$3285,">"&DATE(YEAR(TODAY()),MONTH(TODAY()),0),Logbook!H$3:H$3286)

(previous calendar month)

Code: Select all

=SUMIF(Logbook!$A$3:$A$3285,">"&DATE(YEAR(TODAY()),MONTH(TODAY())-1,0),Logbook!H$3:H$3286)-thisMonth

(last 6 months)

Code: Select all

=SUMIF(Logbook!$A$3:$A$3285,">"&DATE(YEAR(TODAY()),MONTH(TODAY())-6,MIN(DAY(TODAY()),DAY(DATE(YEAR(TODAY()),MONTH(TODAY())-6+1,0)))),Logbook!H$3:H$3286)

so I think your original problem was just a lack of brackets around today()-7