Hi,
I have a little bit of a complicated requirement which I am not sure if its possible to do via Excel VBA or formula, what I need is help with tracking employee sickness days and perform some calculation for any sickness days above their allowance. My excel sheet has 6 columns, (1) Employee number, (2) Employee Name, (3) Sickness start, (4) Sickness end (5) number of days, (6) days outside of allowance.
The first 5 columns will have data but I want the 6th column to be populated by checking if the employee has exceeded their sickness allowance, which is 12 days, and if so any amount above the allowance will be taken from any additional sickness rows and highlight those rows that were amended.
Please see below example, an employee can have multiple instances of absence. In the example below, John Smith has taken 24 days sick leave, so after the allowance of 12 days any additional days will show as days outside of the allowance and highlighted in red. His first two absences are within 12 days so are unaffected but the rest are above the limit so are populated and highlighted. For Sally Jones, her first absence is above the limit of 12 days allowance and so the column is populated with 15 and highlighted. All her subsequent absences are also populated as outside of allowance and highlighted as she is already above the limit.
Hope this makes sense, my file runs into thousands of rows so any advice would be greatly appreciated.
Many thanks
Kaz
I have a little bit of a complicated requirement which I am not sure if its possible to do via Excel VBA or formula, what I need is help with tracking employee sickness days and perform some calculation for any sickness days above their allowance. My excel sheet has 6 columns, (1) Employee number, (2) Employee Name, (3) Sickness start, (4) Sickness end (5) number of days, (6) days outside of allowance.
The first 5 columns will have data but I want the 6th column to be populated by checking if the employee has exceeded their sickness allowance, which is 12 days, and if so any amount above the allowance will be taken from any additional sickness rows and highlight those rows that were amended.
Please see below example, an employee can have multiple instances of absence. In the example below, John Smith has taken 24 days sick leave, so after the allowance of 12 days any additional days will show as days outside of the allowance and highlighted in red. His first two absences are within 12 days so are unaffected but the rest are above the limit so are populated and highlighted. For Sally Jones, her first absence is above the limit of 12 days allowance and so the column is populated with 15 and highlighted. All her subsequent absences are also populated as outside of allowance and highlighted as she is already above the limit.
| Employee number | Employee Name | Absence Start | Absence End | Number of days absent | Days outside of allowance |
12345 | John Smith | 01/02/20 | 10/02/20 | 10 | 0 |
12345 | John Smith | 04/03/20 | 05/03/20 | 2 | 0 |
12345 | John Smith | 15/03/20 | 25/03/20 | 11 | 11 |
12345 | John Smith | 15/04/20 | 15/04/20 | 1 | 1 |
45678 | Sally Jones | 15/01/20 | 10/02/20 | 27 | 15 |
45678 | Sally Jones | 14/03/20 | 15/03/20 | 2 | 2 |
45678 | Sally Jones | 15/04/20 | 15/04/20 | 1 | 2 |
98765 | Peter Smith | 15/03/20 | 26/03/20 | 10 | 0 |
333888 | Mukesh Patel | 01/04/20 | 13/04/20 | 13 | 1 |
Hope this makes sense, my file runs into thousands of rows so any advice would be greatly appreciated.
Many thanks
Kaz
