Excel VBA/formula to track and calculate sickness absence

kaz123

New Member
Joined
Oct 8, 2017
Messages
22
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.

Employee numberEmployee NameAbsence StartAbsence EndNumber of days absentDays 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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,643
Office Version
  1. 365
Platform
  1. Windows
Not sure you have Sally Jones right? Test this on your bigger data

=MIN(E2,MAX(SUMIFS($E$2:E2,$A$2:A2,A2)-12,0))

Edited
 

kaz123

New Member
Joined
Oct 8, 2017
Messages
22
Wow brilliant! thanks a lot, this works very nicely and you made it look very elegant.
 

Forum statistics

Threads
1,144,159
Messages
5,722,833
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top