Hello,
I am trying to find a way to calculate the number of consecutive days that employees were absent due to illness, including weekends.
In the data sample:
We have 2 different cases : for Adeline the payrool has put in the correct sickness absence information including the weekend but for John, the sick leave is entered exluding the weekend.
| Date | Day | ID | Name | contract hours | Absence | Absence Duration | Consecutive absent | Consecutive absence
(- 2 days) | Hours to pay |
| 28.01.2023 | S | 01 | Adeline | 8 | | 0 | 0 | 0 | 8 |
| 29.01.2023 | S | 01 | Adeline | 8 | | 0 | 0 | 0 | 8 |
| 30.01.2023 | M | 01 | Adeline | 0 | Sickness | 8 | 1 | 0 | 8 |
| 31.01.2023 | T | 01 | Adeline | 0 | Sickness | 8 | 2 | 0 | 8 |
| 01.02.2023 | W | 01 | Adeline | 0 | Sickness | 8 | 3 | 1 | 0 |
| 02.02.2023 | T | 01 | Adeline | 0 | Sickness | 8 | 4 | 2 | 0 |
| 03.02.2023 | F | 01 | Adeline | 0 | Sickness | 8 | 5 | 3 | 0 |
| 04.02.2023 | S | 01 | Adeline | 8 | Sickness | 0 | 6 | 4 | 0 |
| 05.02.2023 | S | 01 | Adeline | 8 | Sickness | 0 | 7 | 5 | 0 |
| 06.02.2023 | M | 01 | Adeline | 8 | Sickness | 8 | 8 | 6 | 0 |
| 07.02.2023 | T | 01 | Adeline | 8 | Sickness | 8 | 9 | 7 | 0 |
| 08.02.2023 | W | 01 | Adeline | 8 | Sickness | 8 | 10 | 8 | 0 |
| 09.02.2023 | T | 01 | Adeline | 8 | | 0 | 0 | | 8 |
| 21.01.2023 | F | 02 | Jean DE | 8 | | 0 | 0 | | 8 |
| 22.01.2023 | S | 02 | Jean DE | 8 | | 0 | 0 | | 8 |
| 23.01.2023 | M | 02 | Jean DE | 8 | Sickness | 8 | 1 | 1 | 8 |
| 24.01.2023 | T | 02 | Jean DE | 8 | Sickness | 0 | 0 | | 8 |
| 25.01.2023 | W | 02 | Jean DE | 8 | Sickness | 0 | 0 | | 8 |
| 26.01.2023 | T | 02 | Jean DE | 8 | Sickness | 0 | 0 | | 8 |
| 27.01.2023 | F | 02 | Jean DE | 8 | Sickness | 8 | 1 | 0 | 8 |
| 28.01.2023 | S | 02 | Jean DE | 0 | | 0 | 2 | 0 | 0 |
| 29.01.2023 | S | 02 | Jean DE | 0 | | 0 | 3 | 1 | 0 |
| 30.01.2023 | M | 02 | Jean DE | 8 | Sickness | 8 | 4 | 2 | 8 |
| 31.01.2023 | T | 02 | Jean DE | 8 | Sickness | 8 | 5 | 3 | 0 |
| 01.02.2023 | | 02 | Jean DE | 8 | | 0 | | | 8 |
What I need to have :
- Count consecutive absences of an employee due to sickness, including weekend.
- If an employee was absent on Friday and he's still absent on Monday, the weekend must be counted even if the sikness absence was not entered in the system by the human resources department.
- Finally, I would like to add a column that allows me to count the first 2 days of work to be paid that are not covered by health insurance. (For example, John started getting sick on Friday, so we need to pay his Friday and Monday wages (excluding the weekend which is not a workday).
| Employe | Date absence | Consecutive absence days | Days to be paid | Hours to pay |
| Adeline | 30-01-23 to 08-02-23 | 10 | 2 | 16 hours |
| Jean | 23-01-23 to 23-01-23 | 1 | 1 | 8 hours |
| Jean | 27-01-23 to 31-01-23 | 5 | 2 | 16 hours |
Maybe it would be easier to count sickness absent excluding weekend ??
Thank you for your help.
