Forum Discussion
Employee Absence Schedule - Record Sickness Spells - I am counting all 'S' easy enough
Hi
I am counting all 'S' easy enough which gives me a total count of the sickness days taken but now i want to count up the sickness periods too.
So say Employee 1 has two consecutive days off on Monday & Tuesday (week1) and then just a Monday off sick on week 3 this would be classed as TWO Sickness occurrences not three.
I am using the Microsoft Employee Absence Schedule template, so if someone can just help with the count formula. This current formula is for counting the number of days they have taken =COUNTIF(January[@[1]:[31]],"S"), I need the formula to show a count of 3 in this instance as that's how many sick periods they've taken.
this is the template I'm using, with some slight adjustments.
https://templates.office.com/en-us/employee-absence-schedule-tm03987167
- The problem with [Employee Name] is that you change that column title to [Management] so try:
=SUMPRODUCT((January[@[Management]:[30]]<>"S")*(January[@[1]:[31]]="S"))
that said, this formula will treat F...M sickness as 2 separate instances not 1 long instance that spanned across the weekend unless Sa/Su are also marked "S"
4 Replies
- I see that you have 'S' sick days on weekends too so if we don't have to exclude weekend days (i.e. sick on F and then again on M even though Sa/Su are blank will count as 2 episodes instead of 1) then something like:
=SUMPRODUCT((January[@[Employee Name]:[30]]<>"S")*(January[@[1]:[31]]="S"))- Hi that was my mistake by sticking S's in the table and not taking notice of the weekends! I've removed it now, I can't get that formula to work though, it doesn't accept the employee name at all, the only way I was able to get excel to accept the formula is to use a numerical value instead:
=SUMPRODUCT((January[@[1]:[30]]<>"S")*(January[@[1]:[31]]="S"))
but this just gives me a #N/A- The problem with [Employee Name] is that you change that column title to [Management] so try:
=SUMPRODUCT((January[@[Management]:[30]]<>"S")*(January[@[1]:[31]]="S"))
that said, this formula will treat F...M sickness as 2 separate instances not 1 long instance that spanned across the weekend unless Sa/Su are also marked "S"
