Employee ID | Absence Reason | Start Date | End Date | Department | Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | Jun-21 | Jul-21 | Aug-21 | Sep-21 | Oct-21 | Nov-21 | Dec-21 |
1 | Stress | 01/05/2021 | 18/08/2021 | A | ||||||||||||
2 | COVID | 09/04/2021 | 27/06/2021 | A | ||||||||||||
3 | Stress | 21/07/2021 | 25/07/2021 | B | ||||||||||||
1 | Stress | 20/11/2020 | 09/03/2021 | A | ||||||||||||
1 | COVID | 14/09/2021 | 20/09/2021 | B | ||||||||||||
2 | Other | 02/11/2020 | 24/02/2021 | A | ||||||||||||
2 | Stress | 19/10/2020 | 09/01/2021 | B | ||||||||||||
1 | COVID | 01/12/2020 | 27/02/2022 | B | ||||||||||||
3 | Stress | 05/12/2020 | 18/05/2022 | B | |
In the empty columns that have the month/year fields, I would like to show the number of days missed (net work days) for each of the respective months. So, if for example someone was off for the entirety of January 2021, it would show 31, and so on.
I would also like to be able to count the number of unique employees that have been off (for any length of time) in a given month, below. Again, the rule above would apply: if the start/end dates overlap any months then the would be counted for those months, e.g. if start date was 01/01/2021 and end date was 01/07/2021, then they would be counted in Jan, Feb, Mar, Apr, May, Jun, and July. Thank you.
Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | Jun-21 | Jul-21 | Aug-21 | Sep-21 | Oct-21 | Nov-21 | Dec-21 | |
Unique Employee Number |