Counting instances of sickness excluding the weekend

KV1993

New Member
Joined
May 9, 2023
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I would like to seek help on how I count sick leave instances.

My data are

Column A - Employee Number
Column B - Employee Name
Column C - Sick Leave Date (Newest to Oldest)
Colum D - is where I should put the formula to calculate the instances.

If member is sick leave on Friday until Monday next week it should only be counted as one instance. We have existing formula in our old reports, however, it is counting next week as new instances.

Appreciate any help. Thank you so much.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
please post some sample data using the xl2bb add in (link below).
at minimum a TABLE (not screen shot) of your data, and expected results.
 
Upvote 0
Hi, Thank you so much for your reply. I tried the XL2BB and I can't manage to make it work. I'm so sorry. But, this is the sample data. I can't add the original data sorry.

EE IDNNameSick DateAbsence Instances
54321John20/07/20111
54321John03/10/20112
54321John18/10/20112
54321John19/10/20112
54321John20/10/20112
54321John21/10/20112
67894Emma21/07/20111
67894Emma22/07/20111
67894Emma25/07/20111
67894Emma26/07/20111
67894Emma27/07/20111
67894Emma28/07/20111
67894Emma29/07/20111
55448Betty15/08/20111
55448Betty16/08/20111
55448Betty29/08/20112
99544Carl04/10/20111
99544Carl05/10/20111
99544Carl06/12/20112
99544Carl12/01/20123
99544Carl13/01/20123
99544Carl16/01/20123
99544Carl17/01/20123
99544Carl18/01/20123
33567Andrew19/03/20121
33567Andrew22/03/20121
33567Andrew23/03/20121

I need to count that John has 2 instances of sick leave, Emma - 1, Betty - 2, Carl - 3 and Andrew - 1.
Thank you so much for the help. I sincerely appreciate it.
 
Upvote 0
Not sure how you got those numbers, but how about
Fluff.xlsm
ABCD
1EE IDNNameSick DateAbsence Instances
254321John20/07/20111
354321John03/10/20112
454321John18/10/20113
554321John19/10/20113
654321John20/10/20113
754321John21/10/20113
867894Emma21/07/20111
967894Emma22/07/20111
1067894Emma25/07/20111
1167894Emma26/07/20111
1267894Emma27/07/20111
1367894Emma28/07/20111
1467894Emma29/07/20111
1555448Betty15/08/20111
1655448Betty16/08/20111
1755448Betty29/08/20112
1899544Carl04/10/20111
1999544Carl05/10/20111
2099544Carl06/12/20112
2199544Carl12/01/20123
2299544Carl13/01/20123
2399544Carl16/01/20123
2499544Carl17/01/20123
2599544Carl18/01/20123
2633567Andrew19/03/20121
2733567Andrew22/03/20122
2833567Andrew23/03/20122
Sheet4
Cell Formulas
RangeFormula
D2:D28D2=IF(A2<>A1,1,IF(NETWORKDAYS(C1,C2)=2,D1,D1+1))
 
Upvote 0
Hi Fluff,

Thank you so much for your help! It works! I just manually counted the instances in the sample data. Haha

-KV
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top