formula to count the number occasion

Hi

I'm Hoping someone can help

I am making a attendance record and need a formula that works out the number of sick occasion a employee has

For example an employee is of sick 3 day MON,TUE,WED- this 1 occasion BUT if the employee was sick MON,WED,SAT - this 3 occasion

Also

I also need a formula that counts the number of time a employee is sick before or after a day off.

I attached the excel file

I would appreciate any advice

Hi, and welcome to the forum

Frankly the best advice I can offer is to change the layout.
You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

You should therefore create a database table with columns for
Date
Staff Name
Store Name
Attendance Category i.e. In, Late, Hol...ect