Hi
I'm am going to do some reports about employee absences. For example, absences per month, absence rate per month and cost of absence per month.
My data set looks like this (*calculated using excel, converting to full time days):
As you can see, it's very diverse dataset. How would I be able to calculate total absences per month when some absences are spread between one or more months. Also, for the last example, they techniquely work 4 hours a day so the 8 hours leave is spread between 2 months here, 4 per month for the employee, unfortunately my dataset doesn't show contracted hours per day.
I might be overcomplicating this. But it's a very complex dataset. How would I calculate all absenteeism from 1/06/2021 to 30/06/02022 per month, or even 12 months year end?
Example of what I want:
Thanks
I'm am going to do some reports about employee absences. For example, absences per month, absence rate per month and cost of absence per month.
My data set looks like this (*calculated using excel, converting to full time days):
| Employee ID | Department | Employment Type | Status | Employment Start Date | Employment End Date | Absent Start Date | Absent To Date | Absent Quantity | Absent Unit | Absent in Days* | Reason | Contracted Hours/Week | Rate |
| 123 | B | Full Time | Terminated | 1/02/2020 | 1/12/2021 | 30/05/2021 | 2/06/2021 | 4 | Days | 4 | Annual | 38 | $20 |
| 321 | C | Part Time | Active | 6/05/2000 | 12/08/2021 | 12/08/2021 | 5 | Hours | 0.6579 | Sick | 20 | $19 | |
| 456 | C | Casual | Active | 5/08/1992 | 29/11/2021 | 1/01/2022 | 33 | Days | 33 | Long Service | 0 | $30 | |
| 888 | A | Part Time | Terminated | 20/2/2000 | 20/04/2022 | 31/03/2022 | 1/04/2022 | 8 | Hours | 1.053 | Sick | 16 | $40 |
As you can see, it's very diverse dataset. How would I be able to calculate total absences per month when some absences are spread between one or more months. Also, for the last example, they techniquely work 4 hours a day so the 8 hours leave is spread between 2 months here, 4 per month for the employee, unfortunately my dataset doesn't show contracted hours per day.
I might be overcomplicating this. But it's a very complex dataset. How would I calculate all absenteeism from 1/06/2021 to 30/06/02022 per month, or even 12 months year end?
Example of what I want:
| Month | Number of Absences | Number of Absences by Department A | Number of Absences by Department B | Number of Absences by Department C |
| June 2021 | ||||
| July 2021 | ||||
| August 2021 |
Thanks
