streamline offset calculation, then slave that calculation to auto adjust with month

Hi Team,

This spreadsheet keeps coming up, but thanks to the work of several others who have inputted into this project, this is the last hurdle.

The offset formula below sits in E7 through to X7 and is the formula which looks for patterns over the previous 12 month period. It is looking for the following. Sick (PSL or FAM) before BLP (days off) or Annual Leave (days off). An Ex is a day where the crew is working into that day. For example they may start at 4.00pm and finish at 1.00am the following morning. This is essentially a day off where they cannot be rostered for a second shift before actual days off. This beast is also looking for the following patterns where the driver is booking off on the shift leading into the Ex. It returns the number of times any of these patterns have happened.



=SUM(((COUNTIFS(E128:E524,"PSL",E129:E525,"EX",E130:E526,"BLP")+COUNTIFS(E128:E524,"PSL",E129:E525,"EX",E130:E526,"AL")+COUNTIFS(E128:E524,"FAM",E129:E525,"EX",E130:E526,"AL")+COUNTIFS(E128:E524,"FAM",E129:E525,"EX",E130:E526,"BLP")+COUNTIFS(E128:E524,"PSL",E129:E525,"BLP")+COUNTIFS(E128:E524,"BLP",E129:E525,"PSL"))+(COUNTIFS(E128:E524,"FAM",E129:E525,"BLP")+COUNTIFS(E128:E524,"BLP",E129:E525,"FAM"))+(COUNTIFS(E128:E524,"PSL",E129:E525,"AL")+COUNTIFS(E128:E524,"AL",E129:E525,"PSL"))+(COUNTIFS(E128:E524,"FAM",E129:E525,"AL")+COUNTIFS(E128:E524,"AL",E129:E525,"FAM"))+(COUNTIFS(E128:E524,"FAM",E129:E525,"LSL")+COUNTIFS(E128:E524,"LSL",E129:E525,"FAM"))+(COUNTIFS(E128:E524,"PSL",E129:E525,"LSL")+COUNTIFS(E128:E524,"LSL",E129:E525,"PSL")))-SUM(COUNTIFS(E128:E524,"BLP",E129:E525,"PSL",E130:E526,"AL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"FAM",E130:E526,"AL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"PSL",E130:E526,"LSL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"FAM",E130:E526,"LSL")+(COUNTIFS(E128:E524,"LSL",E129:E525,"PSL",E130:E526,"BLP")+(COUNTIFS(E128:E524,"LSL",E129:E525,"FAM",E130:E526,"BLP")+(COUNTIFS(E128:E524,"AL",E129:E525,"FAM",E130:E526,"BLP")+(COUNTIFS(E128:E524,"AL",E129:E525,"PSL",E130:E526,"BLP"))/2))))))))



For the sake of simplicity I will call the boldfaced portion of the algorithm Frankenstein.

All the row numbers do is tell the computer to look between them as these represent the prior 12 months’ worth dates. If I removed them in favour of telling the computer to look at the previous 12 months of dates only then it would look like this…



=SUM(((COUNTIFS(Insert Frankenstein - numbers here)},$D$8:$D$1294,">"&EOMONTH(TODAY()-DAY(TODAY()),-12),$D$8:$D$1294,"<="&EOMONTH(TODAY(),0)))



Expanded I imagined it would look like this…



=SUM(COUNTIFS(E$8:E$1294({"PSL","EX","BLP"},{"PSL","EX","AL"},{"FAM","EX","AL"},{"FAM","EX","BLP"},{"PSL","BLP"},{"BLP","PSL"},{"FAM","BLP"},{"BLP","FAM"},{"PSL","AL"},{"AL","PSL"},{"FAM","AL"},{"AL","FAM"},{"FAM","LSL"},{"LSL","FAM"},{"PSL","LSL"},{"LSL","PSL"},{"BLP",”PSL","AL"},{"BLP","FAM","AL"},{"BLP","PSL","LSL"},{"BLP","FAM","LSL"},{"LSL","PSL","BLP"},{"LSL","FAM","BLP"},{"AL","FAM","BLP"},{"AL","PSL","BLP"})/2),$D$8:$D$1294,">"&EOMONTH(TODAY()-DAY(TODAY()),-12),$D$8:$D$1294,"<="&EOMONTH(TODAY(),0)))



While this does not give an outright error, it does not appear to recognize the calculations.

Am I on the right track or would it be better off redesigning and streamlining Frankenstein so that it is not so clunky and then wrapping the “slave to computer clock offset” around it?

What I am trying to achieve here is to have the entire spreadsheet row 7 automatically adjust at the beginning of each month the same as row 5 and 6.

Copy of spreadsheet attached so you can see it in operation.

Thank you all for your assistance once again.

Regards

Huntsman