Hello
I'm am going to do some reports about emploee abscences. For example, absences per month, abscence 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 | Absenet 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 diversed dataset. How would I be able to calcuate total absences per month when some absenses 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 caluclate all absentism from 1/06/2021 to 30/06/02022 per month, or even 12 months year end?
Thanks
