Hi Team,
Firstly, my apologies for the length of this post. I included as much information as I could however.
I have designed a spreadsheet that monitors depot annual leave and absenteeism patterns for approximately 100 people. Coded abbreviation's are entered directly into the spreadsheet by the duty supervisor when ever leave is applied for or someone books off sick.
The abbreviations I am concerned with are EX (which is a working cross over shift), PSL for Personal Sick Leave, FAM for illness in Family, AL for Approved Leave and BLP for Block Leisure Period or rostered days off.
Only one abbreviation can be entered into any one cell at any one time. (The surrounding spread sheet is usually locked out to prevent accidental interference).
Through the help of this and other forums, I have managed to tweak the spread sheet to a point where it is fully automated as far as looking at absenteeism (PSL and FAM) for the current month and the past 12 months.
This has been achieved by clever use the algorithms in rows 4,5 and 6 and works perfectly in slaving the simple offset to the computer clock so it adjusts with the beginning of each month.
Now for the knotty part called "the beast". This is row 7.
So that the spread sheet can be a stand alone spread sheet without modifications on a monthly basis I am trying to slave the formula in row 7 to the computer clock to do what the previous rows are doing, i.e. adjusting automatically at the beginning of a new month.
Below is offset formula "The Beast".
Part A
(((COUNTIFS(E128:E524,"PSL",E129:E525,"EX",E130:E526,"AL")+COUNTIFS(E128:E524,"FAM",E129:E525,"EX",E130:E526,"AL")+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"))
minus
Part B
SUM(COUNTIFS(E128:E524,"BLP",E129:E525,"PSL",E130:E526,"AL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"FAM",E130:E526,"AL")+(COUNTIFS(E128:E524,"AL",E129:E525,"FAM",E130:E526,"BLP")+(COUNTIFS(E128:E524,"AL",E129:E525,"PSL",E130:E526,"BLP")))))))
Part A counts any permutation of an absenteeism day (sickie) before or after Approved Leave or Rostered days off. Part A is looking for the following patterns...
PSL-EX-AL, FAM-EX-AL, PSL-AL, FAM-AL, AL-PSL, AL-FAM, PSL-BLP, FAM-BLP, BLP-PSL and BLP-FAM.
Part B counts and then subtracts from the count on part A the permutations below so that if any of them overlap one of the above permutations, they are counted only once in the row 7 total so that I do not get double ups. Part B is looking for the following patterns...
BLP-PSL-AL, BLP-FAM-AL, AL-PSL-BLP and AL-FAM-BLP
Abbreviated the whole thing looks like this - SUM(Beast Part A)-SUM(Beast Part B)
Attached spread sheet logic:
Row 4: Counts days. (Self adjusting slaved to computer clock)
=SUM(COUNTIFS(E$10:E$1296,{"PSL","FAM"},$D$10:$D$1296,">"&EOMONTH(TODAY(),-1),$D$10:$D$1296,"<="&EOMONTH(TODAY(),0)))
Row 5: Counts past 12 months. (Self adjusting slaved to computer clock)
=SUM(COUNTIFS(E$10:E$1296,{"PSL","FAM"},$D$10:$D$1296,">"&EOMONTH(TODAY()-DAY(TODAY()),-12),$D$10:$D$1296,"<="&EOMONTH(TODAY(),0)))
Row 6:
Counts number of absence occurrences past 12 months. (Self adjusting slaved to computer clock)
=SUMPRODUCT((E$11:E$1297={"PSL","FAM","MCL"})*(E$11:E$1297<>E$10:E$1296)*($D$11:$D$1297>EOMONTH(TODAY()-DAY(TODAY()),-12)))
Row 7: =THE BEAST
THE BEAST counts the number of absenteeism occurrences before and after approved leave or rostered days off from the 01st day of the current month last year, to the last day of the current month this year. In essence it will cover a maximum of 396 days. E.g. from 01/08/2013 to 31/08/2014.
At the moment, this is manually worked out by adjusting the start point of the offset algorithm forwards by the number of days in the current month and doing the same to the stop point. Essentially the stop point is moot as this is in the future and this can take as little as 5 minutes on the spread sheet template that I use. (I then copy and paste the adjusted formula across the other 8 spread sheet pages which are all identical)
Using August as a model, I envisage that the logic would look something like this…
=SUM(COUNTIF(BEAST Part A)-SUM(COUNTIF(BEAST Part B)))
TODAY()-365)+31)+(TODAY()+30))
The programming that I am coming unstuck with is in separating the patterns I want Excel to look for (BEASTS A and B) and telling Excel where to look for them depending on current date ($D$9:$D$1295>EOMONTH(TODAY()-DAY(TODAY()),-12)).
Finally I come to the question. (My apologies for the time it took).
Is there a way to wrap a self adjusting formula slaved to the computer clock around “THE BEAST” so that it keeps track of the month and slides along when the spread sheet is opened at the beginning of a new month?
Patterns can be counted separately in their own cell eg row 8 and 9 are free so if need be the calculation of part A could be done in cell E8 and part B in E9 with the E7=SUM(E8-E9).
Is such a calculation formula possible without resorting to Macros or am I doomed to work this out manually at the beginning of each month?
I have playing around with various combinations of the formulas in ROWS 4, 5 and 6 but I had help in achieving this so I am not even sure how they work. I am just grateful for the author of them and that they do work. I have been toying with this for months now and I am at a dead end.
Please find attached a template spread sheet for experimenting on. This abbreviated version is what is currently in use.
Thank you all for your time and assistance.
Warm Regards - Delta
Firstly, my apologies for the length of this post. I included as much information as I could however.
I have designed a spreadsheet that monitors depot annual leave and absenteeism patterns for approximately 100 people. Coded abbreviation's are entered directly into the spreadsheet by the duty supervisor when ever leave is applied for or someone books off sick.
The abbreviations I am concerned with are EX (which is a working cross over shift), PSL for Personal Sick Leave, FAM for illness in Family, AL for Approved Leave and BLP for Block Leisure Period or rostered days off.
Only one abbreviation can be entered into any one cell at any one time. (The surrounding spread sheet is usually locked out to prevent accidental interference).
Through the help of this and other forums, I have managed to tweak the spread sheet to a point where it is fully automated as far as looking at absenteeism (PSL and FAM) for the current month and the past 12 months.
This has been achieved by clever use the algorithms in rows 4,5 and 6 and works perfectly in slaving the simple offset to the computer clock so it adjusts with the beginning of each month.
Now for the knotty part called "the beast". This is row 7.
So that the spread sheet can be a stand alone spread sheet without modifications on a monthly basis I am trying to slave the formula in row 7 to the computer clock to do what the previous rows are doing, i.e. adjusting automatically at the beginning of a new month.
Below is offset formula "The Beast".
Part A
(((COUNTIFS(E128:E524,"PSL",E129:E525,"EX",E130:E526,"AL")+COUNTIFS(E128:E524,"FAM",E129:E525,"EX",E130:E526,"AL")+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"))
minus
Part B
SUM(COUNTIFS(E128:E524,"BLP",E129:E525,"PSL",E130:E526,"AL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"FAM",E130:E526,"AL")+(COUNTIFS(E128:E524,"AL",E129:E525,"FAM",E130:E526,"BLP")+(COUNTIFS(E128:E524,"AL",E129:E525,"PSL",E130:E526,"BLP")))))))
Part A counts any permutation of an absenteeism day (sickie) before or after Approved Leave or Rostered days off. Part A is looking for the following patterns...
PSL-EX-AL, FAM-EX-AL, PSL-AL, FAM-AL, AL-PSL, AL-FAM, PSL-BLP, FAM-BLP, BLP-PSL and BLP-FAM.
Part B counts and then subtracts from the count on part A the permutations below so that if any of them overlap one of the above permutations, they are counted only once in the row 7 total so that I do not get double ups. Part B is looking for the following patterns...
BLP-PSL-AL, BLP-FAM-AL, AL-PSL-BLP and AL-FAM-BLP
Abbreviated the whole thing looks like this - SUM(Beast Part A)-SUM(Beast Part B)
Attached spread sheet logic:
Row 4: Counts days. (Self adjusting slaved to computer clock)
=SUM(COUNTIFS(E$10:E$1296,{"PSL","FAM"},$D$10:$D$1296,">"&EOMONTH(TODAY(),-1),$D$10:$D$1296,"<="&EOMONTH(TODAY(),0)))
Row 5: Counts past 12 months. (Self adjusting slaved to computer clock)
=SUM(COUNTIFS(E$10:E$1296,{"PSL","FAM"},$D$10:$D$1296,">"&EOMONTH(TODAY()-DAY(TODAY()),-12),$D$10:$D$1296,"<="&EOMONTH(TODAY(),0)))
Row 6:
Counts number of absence occurrences past 12 months. (Self adjusting slaved to computer clock)
=SUMPRODUCT((E$11:E$1297={"PSL","FAM","MCL"})*(E$11:E$1297<>E$10:E$1296)*($D$11:$D$1297>EOMONTH(TODAY()-DAY(TODAY()),-12)))
Row 7: =THE BEAST
THE BEAST counts the number of absenteeism occurrences before and after approved leave or rostered days off from the 01st day of the current month last year, to the last day of the current month this year. In essence it will cover a maximum of 396 days. E.g. from 01/08/2013 to 31/08/2014.
At the moment, this is manually worked out by adjusting the start point of the offset algorithm forwards by the number of days in the current month and doing the same to the stop point. Essentially the stop point is moot as this is in the future and this can take as little as 5 minutes on the spread sheet template that I use. (I then copy and paste the adjusted formula across the other 8 spread sheet pages which are all identical)
Using August as a model, I envisage that the logic would look something like this…
=SUM(COUNTIF(BEAST Part A)-SUM(COUNTIF(BEAST Part B)))
TODAY()-365)+31)+(TODAY()+30))The programming that I am coming unstuck with is in separating the patterns I want Excel to look for (BEASTS A and B) and telling Excel where to look for them depending on current date ($D$9:$D$1295>EOMONTH(TODAY()-DAY(TODAY()),-12)).
Finally I come to the question. (My apologies for the time it took).
Is there a way to wrap a self adjusting formula slaved to the computer clock around “THE BEAST” so that it keeps track of the month and slides along when the spread sheet is opened at the beginning of a new month?
Patterns can be counted separately in their own cell eg row 8 and 9 are free so if need be the calculation of part A could be done in cell E8 and part B in E9 with the E7=SUM(E8-E9).
Is such a calculation formula possible without resorting to Macros or am I doomed to work this out manually at the beginning of each month?
I have playing around with various combinations of the formulas in ROWS 4, 5 and 6 but I had help in achieving this so I am not even sure how they work. I am just grateful for the author of them and that they do work. I have been toying with this for months now and I am at a dead end.
Please find attached a template spread sheet for experimenting on. This abbreviated version is what is currently in use.
Thank you all for your time and assistance.
Warm Regards - Delta
