Excel Rolling 2-year totals
 

EXCEL Question: How to I check that my totals over the past 730 days (rolling 2 years) exceed the mandatory 12 hours? I’ve got as far as plotting 2 years ago (easy: TODAY()-730), finding the first subsequent flight date (INDEX… MATCH…), and retrieving the hours flown on that flight (VLOOKUP). But I can’t find a way to sum all the hours from that flight to now. I’ve resorted to the million monkeys technique but I clearly don’t have enough monkeys or typewriters. There must be a way, pleeeeease.
PS I don’t care if its decimal hours, I can manage that; real hours:minutes would only be the cherry on a cake which I can’t bake.
PPS SUMIF works but only for the latest date; it must be possible to look back and see when I got close to the limit

Cancel my question, the million monkeys and Google finally sorted it. If you’re interested:
Line 1: titles
Lines 2 and lower:
Column A: flight date
Column B: flight duration
Column C: Date 2 years ago (Col A minus 730)
Column D: {=INDEX($A$2:$A2,MATCH(TRUE,$A$2:$A2>C2,0))} [enter the formula less the outer squirly brackets them press Control, Shift and Enter to enter it; the squirly brackets will return] [it’s an array, whatever that is]
Column E: =SUM(INDIRECT(CELL("address",INDEX(A$2:D2,MATCH(D2,A$2:A2,0) ,2))):B2) except when I post that, an emoji appears in place of "colon D" so change it back.
Then copy that line down as many lines as you need and enter your own flight details in columns A and B in date order.
It won’t operate until you’ve entered at least 2 years data, and then only for the flights from 2 years after the first flight

But it works
Thank you and good night!

Have a look here: https://contexturesblog.com/archives...otal-in-excel/

Together with what you have tried already, this might do the trick. As long as all your entries are in hours:minutes, just formatting the cell where you want the answer to appear as h:mm should leave you with something close to what you want.

As an Excel user that approach simply offends my eyes. :yuk:

The answer to your problem is array multiplication. Please try the following array formula in column D:

Assuming your last row with data is row 999:

{=SUM(($B$2:$B$999)*(($A$2:$A999)>=C2))}

For the purists, I would use named ranges rather than absolute references, but that is a lesson for another day. For the moment, just remember that you have to update the last row reference whenever you add more data rows.

HTH

EG

Thanks EG. Sorry for the offence but also happy - it means you clearly understand arrays; I don’t, I just stumbled into them. I hoped an expert would find a more elegant solution and had almost given up waiting. And what an elegant solution. It took me a while to get it, but I’ve tinkered it so that I can copy down the line above for each new flight. Hunky dory
Sorry I’ve been late getting back – busy week