Consecutive Occasions discounting weekends

**harrietday17** · 09-24-2022, 06:41 AM

Hi everyone,

I hope you can help me out. I have watched numerous videos, read multiple forums and help pages and I can't figure out the formula I need.

My company have launched a new policy for absence management with new company sick pay entitlements and trigger points, I have been tasked with creating the absence tracker to reflect this for all employees.

I have attached what I have done so far, the part I am struggling with is the trigger points, if an employee like employee 9 is off on LTS it counts the occasions as multiple occasions as we do not enter data on the weekend dates but my formula will count this as multiple occasions I need to count as 1 occasion as the total days off in a year is correct.

I have tried changing the formula in the days off in a year so we enter the data on a Saturday and Sunday, as it gets the occasions right but then the total days off is too many, i tried to correct that by /365 * 260 but then that is wrong from people who are not on LTS.

Please can you help me find a formula to count the occasions as continuous if they present like the above? I may have to throw my laptop out of the window if no one can help me!

Thank you soooooo much in advance

Absence Tracker for Help.xlsx

**Pete_UK** · 09-24-2022, 08:00 AM

Can't you just omit the weekend dates from those on row 2 (starting from O2)? You can generate these if you have a weekday in cell O2 by using this formula in P2:

=O2+IF(WEEKDAY(O2,2)=5,3,1)

then copy across. You might like to change the formula in O3 to this:

=LEFT(TEXT(O2,"ddd"),2)

then copy this across. Obviously, you won't need as many columns to get to the end of the year, so you might have the change where the months appear on row 1.

Hope this helps.

Pete

**harrietday17** · 09-24-2022, 08:20 AM

Hi Pete,

Unfortunately, I need to have the weekends in as we have shifts where people where weekends too, so will need to log if people on that shift are sick too

Will the above formulas work if I keep the weekends in ?

**Pete_UK** · 09-24-2022, 08:34 AM

You can just use this in P2:

=O2+1

if you want to include every day. The other formula (in O3) just gives you the day of the week as a 2-character day, so you don't need to adjust the column width to see them.

Hope this helps.

Pete

**harrietday17** · 09-24-2022, 08:53 AM

Hi Pete,

I just tried the formula you suggested and not sure I portrayed what I was looking for very well.

I am trying to correct my formula in J to count occasions of absence in the last 12 weeks which I have managed to do. However if an employee for instance Employee 9 has been off sick since June he is showing as 13 occasions of absence in the last 12 weeks but I need it count each occasion with a break as 1 occasion?

Hope that makes sense?

Thanks

Not sure if that makes sense?

**etaf** · 09-24-2022, 08:57 AM

i have just tried a formula array - but it slows the spreadsheet down to unusable - i suspect you may need VBA
=IF(A4="","",SUM((FREQUENCY(IF(O4:NO4="S",COLUMN(O4:NO4)),IF(IF((TEXT(O$3:NO$3,"ddd")<>"Sat")*(TEXT(O$3:NO$3,"ddd")<>"Sun"),O4:NO4,"S")<>"S",COLUMN(O4:NO4)))>0)*1))
this will also need to be modified for the weeks you are after
if you use
=IF(A4="","",SUM((FREQUENCY(IF(O4:APB4="S",COLUMN(O4:APB4)),IF(IF((TEXT(O$3:APB$3,"ddd")<>"Sat")*(TEXT(O$3:APB$3,"ddd")<>"Sun"),O4:APB4,"S")<>"S",COLUMN(O4:APB4)))>0)*1))
unusable

A UDF (VBA may help) - I do not provide solutions in vba

see comments for formula