Accumulated Time over Periods with Resets

[JCH136]

I'm working on a spreadsheet to manage pilot flight hour monitoring and working around associated rules.

Simple part - a pilot can fly a maximum of 50hrs in 7 hours, the total does not reset with days off, only after 7 days (i.e. column E is a simple Cell E14 =SUM(D8:D14) which works)

The challenge is with the below formula sets:
A pilot can fly a maximum of 170 accumulated hours in a 28 day period, however their total resets to 0 with 5 rostered days "OFF" (per column)
A pilot can fly a maximum of 450 accumulated hours in a 90 day period, however their total resets to 0 with 5 rostered days "OFF"
A pilot can fly a maximum of 1,200 accumulated hours in a 365 day period, however their total resets to 0 with 28 rostered days "OFF"

I can create simple formula like the above for the 7 days, however this won't capture the reset. I've tried creating a "COUNTIF" formula to display a "0" in columns F, G, H, with 3 "OFF" events in Column C (i.e. F31 =IF(COUNTIF(C30:C32,"OFF")=3,0,SUM(D5:D32)) however this doesn't take into account the resets either; tried variants with say F28+D29, but this won't account the accumulated totals over the 28 days period.

Part of the challenge is that a pilot might rest multiple times within the 28, 90 or 365 days period.

Any assistance or directions that might work would be appreciated; as i'm stumped and a bit too rusty on Visual Basic to go down that path...

Thanks in advance.

 

[James006]

Hi,

You could make your life A LOT easier with a well designed Flat Database ...