Identifying

H

healie2204

New Member
Joined
Jan 9, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Hello all,

I'd be hugely appreciative if anyone could tell me if it's possible to do the following, and if so how to go about it.

I'm trying to calculate the number of days between a number of short term sickness absence episodes and a long term episode that falls afterwards to try and identify if there can be any patterns attributed to short term absence preceding long term within 12 months. My dataset contains a number of individuals with both long and short term absence, and whilst I've been able to work out how to identify each instance of long term, and calculate the days between the closest preceding short term to this, I can't figure out how to then count any other short term episodes (end date to start date of the following long term) that occur prior?

The example in Row 21 for example has 3 long term episodes, with a number of short terms in between them. I need to be able to calculate the number of days between Q22,Q23,Q24 and Q25 to P21, then also calculate the next grouping starting on row 26 - I've gotten as far as this in Column M to calculate the closest short term to long term =IF(AND(J21="LT & ST",C21=C22),DATEDIF(Q22,P21,"D"),"") but don't have the knowledge to achieve the next step.

Any advice would be greatly received.

Thank you,
Healie

1578588146114.png
 

Attachments

  • 1578588119924.png
    1578588119924.png
    105.4 KB · Views: 7

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
HI,

Welcome to the Board!

Just to make sure what you want and if it makes sense. How do you want to make use of the patterns you evetually discover?

J.Ty.
 
Upvote 0
J.Ty. said:
HI,

Welcome to the Board!

Just to make sure what you want and if it makes sense. How do you want to make use of the patterns you evetually discover?

J.Ty.
Click to expand...

Hi J.Ty.,

Before I explain is there any way I can edit the title of the thread? I jumped ahead of myself and got into the detail before I’d even finished the title.

I’m hoping to be able to provide a breakdown of the Long Term episodes in relation to any preceding Short Term, so for example:

LT without ST in the preceding 12 months accounts for XX% of all LT episodes.

LT with 1 episode of ST in the preceding 12 months accounts for XX% of all LT episodes

LT with 2 episodes of ST in the preceding 12 months accounts for Xx% of all LT episodes.

I hope this makes more sense.
Thanks,
H
 
Upvote 0
I see.
However, you wrote earlier "My dataset contains a number of individuals with both long and short term absence". I was afraid you were to ignore cases of LT without any ST, and of ST without any LT, which would produce a bias.

Can you, please, download XL2BB add-in and post the data here? I would like to play with it, but I do not want to retype everything from the image.

J.Ty.
 
Upvote 0
I hope this works:

Book1
ABCDFHIJKLMNOPQ
1Last NameFirst NameEmployee NumberEmployee Has LT 28Classification Type (2)ConcatenateIS LTLT with STLT OnlyLT StatusLT w ST Length BetweenLong Term Start DateLT w ST in 12mAbsence Start DateAbsence End Date
2StevensSam1176771110767700Short Term (27-)10767700Short Term (27-)  23/09/201924/09/2019
3StevensSam1176771110767700Short Term (27-)10767700Short Term (27-)  04/06/201820/06/2018
4FosterAndrew1176771210767712Short Term (27-)10767712Short Term (27-)  04/12/201804/12/2018
5FosterAndrew1176771210767712Short Term (27-)10767712Short Term (27-)  17/11/201717/11/2017
6FosterAndrew1176771210767712Short Term (27-)10767712Short Term (27-)  07/06/201712/06/2017
7FosterAndrew1176771210767712Short Term (27-)10767712Short Term (27-)  18/04/201718/04/2017
8FosterAndrew1176771210767712Long Term (28+)10767712Long Term (28+)YESLT & STLT & ST42419/01/201619/02/2016
9FosterAndrew1176771210767712Short Term (27-)10767712Short Term (27-)  17/11/201421/11/2014
10BeattieJorge1176772110767721Short Term (27-)10767721Short Term (27-)  13/11/201816/11/2018
11BeattieJorge1176772110767721Short Term (27-)10767721Short Term (27-)  27/11/201708/12/2017
12BeattieJorge1176772110767721Short Term (27-)10767721Short Term (27-)  18/07/201720/07/2017
13BeattieJorge1176772110767721Short Term (27-)10767721Short Term (27-)  07/12/201509/12/2015
14TurnbullCharlotte1176773410767734Short Term (27-)10767734Short Term (27-)  11/06/201911/06/2019
15TurnbullCharlotte1176773410767734Short Term (27-)10767734Short Term (27-)  10/11/201715/11/2017
16TurnbullCharlotte1176773410767734Short Term (27-)10767734Short Term (27-)  22/09/201623/09/2016
17TurnbullCharlotte1176773410767734Short Term (27-)10767734Short Term (27-)  29/02/201601/03/2016
18TurnbullCharlotte1176773410767734Long Term (28+)10767734Long Term (28+)YES LT 21/08/201523/11/2015
19TunnSima1176774110767741Long Term (28+)10767741Long Term (28+)YESLT & STLT & ST4821/01/201917/02/2019
20TunnSima1176774110767741Short Term (27-)10767741Short Term (27-)  03/12/201804/12/2018
21CoffeyKatie1176774610767746Long Term (28+)10767746Long Term (28+)YESLT & STLT & ST2842026615/04/201929/09/2019
22CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  22/10/201823/10/2018
23CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  29/03/201803/04/2018
24CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  04/10/201709/10/2017
25CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  28/06/201730/06/2017
26CoffeyKatie1176774610767746Long Term (28+)10767746Long Term (28+)YESLT & STLT & ST17305/10/201619/02/2017
27CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  14/04/201615/04/2016
28CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  26/01/201629/01/2016
29CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  10/12/201514/12/2015
30CoffeyKatie1176774610767746Long Term (28+)10767746Long Term (28+)YESLT & STLT & ST1103/03/201509/06/2015
31CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  03/02/201520/02/2015
32CoffeyKatie1176774610767746Short Term (27-)10767746Short Term (27-)  06/11/201407/11/2014
33Gascoigne-StroughtonJoe1176778710767787Short Term (27-)10767787Short Term (27-)  21/01/201922/01/2019
34Gascoigne-StroughtonJoe1176778710767787Long Term (28+)10767787Long Term (28+)YESLT & STLT & ST83801/08/201808/10/2018
35Gascoigne-StroughtonJoe1176778710767787Short Term (27-)10767787Short Term (27-)  14/04/201615/04/2016
36Gascoigne-StroughtonJoe1176778710767787Short Term (27-)10767787Short Term (27-)  21/08/201523/08/2015
37Gascoigne-StroughtonJoe1176778710767787Short Term (27-)10767787Short Term (27-)  11/02/201513/02/2015
38WatsonJim1176784610767846Short Term (27-)10767846Short Term (27-)  03/04/201912/04/2019
39WatsonJim1176784610767846Short Term (27-)10767846Short Term (27-)  28/03/201902/04/2019
40WatsonJim1176784610767846Short Term (27-)10767846Short Term (27-)  31/12/201814/01/2019
41WatsonJim1176784610767846Short Term (27-)10767846Short Term (27-)  21/03/201821/03/2018
42WatsonJim1176784610767846Short Term (27-)10767846Short Term (27-)  04/08/201704/08/2017
43StokerFred1176785310767853Short Term (27-)10767853Short Term (27-)  27/04/201830/04/2018
44StokerFred1176785310767853Short Term (27-)10767853Short Term (27-)  02/01/201804/01/2018
45StokerFred1176785310767853Short Term (27-)10767853Short Term (27-)  16/11/201719/11/2017
46StokerFred1176785310767853Long Term (28+)10767853Long Term (28+)YESLT & STLT & ST32211/05/201713/06/2017
47StokerFred1176785310767853Short Term (27-)10767853Short Term (27-)  20/06/201623/06/2016
48StokerFred1176785310767853Short Term (27-)10767853Short Term (27-)  09/02/201611/02/2016
49StokerFred1176785310767853Short Term (27-)10767853Short Term (27-)  09/02/201513/02/2015
50GibsonPaul1176787510767875Short Term (27-)10767875Short Term (27-)  03/12/201803/12/2018
51GibsonPaul1176787510767875Long Term (28+)10767875Long Term (28+)YESLT & STLT & ST29817/07/201703/09/2017
52GibsonPaul1176787510767875Short Term (27-)10767875Short Term (27-)  19/09/201622/09/2016
53GibsonPaul1176787510767875Short Term (27-)10767875Short Term (27-)  08/06/201608/06/2016
54GibsonPaul1176787510767875Short Term (27-)10767875Short Term (27-)  05/01/201505/01/2015
55WealleansAndy1176787810767878Long Term (28+)10767878Long Term (28+)YES LTLT 02/02/201511/04/2015
56BeavisKelly1176791110767900Long Term (28+)10767900Long Term (28+)YESLT & STLT & ST72506/03/201817/03/2019
57BeavisKelly1176791110767900Short Term (27-)10767900Short Term (27-)  07/03/201611/03/2016
58BeavisKelly1176791110767900Short Term (27-)10767900Short Term (27-)  16/12/201517/12/2015
Sheet1
Cell Formulas
RangeFormula
M2:M20, M22:M58M2=IF(AND(J2="LT & ST",C2=C3),DATEDIF(Q3,P2,"D"),"")
J2:J58J2=IF(AND(D2=D3,F2="Long Term (28+)"), "LT & ST"," ")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:Q58Expression=AND(LEN($A2)>0,MOD($E2,2)=0)textNO
 
Upvote 0
It works!

Now about the task.

  1. I guess that for each person, no two absences overlap. Can it happen that the next one begins exactly the same day the previous one ends? How to treat them if that happens?
  2. Do you want to count the ST absences preceding an LT one, or rather sum up their lengths?
  3. Should counting/summing reset to 0 after the folowing LT absence, or not?
Please be as specific as possible about things that should be computed.

J.Ty.
 
Upvote 0
J.Ty. said:
It works!

Now about the task.

  1. I guess that for each person, no two absences overlap. Can it happen that the next one begins exactly the same day the previous one ends? How to treat them if that happens?
  2. Do you want to count the ST absences preceding an LT one, or rather sum up their lengths?
  3. Should counting/summing reset to 0 after the folowing LT absence, or not?
Please be as specific as possible about things that should be computed.

J.Ty.
Click to expand...
So in the simplest terms, I need to know how many ST occur in the 12 months prior to any LT.

To answer your questions:
1. The absences don’t overlap, there could potentially be an instance where a ST has an end date that is exactly one day before a LT starts, but this should be handled in the exact same way - it’s a ST episode that occurs within 12 months of the LT starting so should be a count of 1.

2. I want to count the ST that precede a LT within 12 months of the LT start date.

3. Yes, for simplicity, the counting of ST should reset to 0 after each LT.

Thanks again for your assistance J.Ty
 
Upvote 0
Here it is.

  1. My understanding of "an ST within 12 months before ST" is that the end day of ST is at most 365 before start day of LT (so that I do not account for leap years).
  2. The complete data has been sorted by Last name, First name, Absence End Date, in this order of importance. Therefore absences for each preson constitute a block of rows, within which the absence end dates are increasing.
  3. I have replaced full names by initials, in order to avoid processing sensitive data of real people.
  4. A few columns are hidden.
  5. My implementation is here, you can play with it.
Book1
ABCDENOPQR
1Last NameFirst NameEmployee NumberEmployee Has LT 28Classification Type (2)Absence Start DateAbsence End DateCount of STNumber of ST before LTNumber of ST within 12 months before LT
2FA1176771210767712Short Term (27-)2014-11-172014-11-211  
3FA1176771210767712Long Term (28+)2016-01-192016-02-19010
4FA1176771210767712Short Term (27-)2017-04-182017-04-181  
5FA1176771210767712Short Term (27-)2017-06-072017-06-122  
6FA1176771210767712Short Term (27-)2017-11-172017-11-173  
7FA1176771210767712Short Term (27-)2018-12-042018-12-044  
8WA1176787810767878Long Term (28+)2015-02-022015-04-110  
9TC1176773410767734Long Term (28+)2015-08-212015-11-230  
10TC1176773410767734Short Term (27-)2016-02-292016-03-011  
11TC1176773410767734Short Term (27-)2016-09-222016-09-232  
12TC1176773410767734Short Term (27-)2017-11-102017-11-153  
13TC1176773410767734Short Term (27-)2019-06-112019-06-114  
14SF1176785310767853Short Term (27-)2015-02-092015-02-131  
15SF1176785310767853Short Term (27-)2016-02-092016-02-112  
16SF1176785310767853Short Term (27-)2016-06-202016-06-233  
17SF1176785310767853Long Term (28+)2017-05-112017-06-13031
18SF1176785310767853Short Term (27-)2017-11-162017-11-191  
19SF1176785310767853Short Term (27-)2018-01-022018-01-042  
20SF1176785310767853Short Term (27-)2018-04-272018-04-303  
21WJ1176784610767846Short Term (27-)2017-08-042017-08-041  
22WJ1176784610767846Short Term (27-)2018-03-212018-03-212  
23WJ1176784610767846Short Term (27-)2018-12-312019-01-143  
24WJ1176784610767846Short Term (27-)2019-03-282019-04-024  
25WJ1176784610767846Short Term (27-)2019-04-032019-04-125  
26GSJ1176778710767787Short Term (27-)2015-02-112015-02-131  
27GSJ1176778710767787Short Term (27-)2015-08-212015-08-232  
28GSJ1176778710767787Short Term (27-)2016-04-142016-04-153  
29GSJ1176778710767787Long Term (28+)2018-08-012018-10-08030
30GSJ1176778710767787Short Term (27-)2019-01-212019-01-221  
31BJ1176772110767721Short Term (27-)2015-12-072015-12-091  
32BJ1176772110767721Short Term (27-)2017-07-182017-07-202  
33BJ1176772110767721Short Term (27-)2017-11-272017-12-083  
34BJ1176772110767721Short Term (27-)2018-11-132018-11-164  
35CK1176774610767746Short Term (27-)2014-11-062014-11-071  
36CK1176774610767746Short Term (27-)2015-02-032015-02-202  
37CK1176774610767746Long Term (28+)2015-03-032015-06-09022
38CK1176774610767746Short Term (27-)2015-12-102015-12-141  
39CK1176774610767746Short Term (27-)2016-01-262016-01-292  
40CK1176774610767746Short Term (27-)2016-04-142016-04-153  
41CK1176774610767746Long Term (28+)2016-10-052017-02-19033
42CK1176774610767746Short Term (27-)2017-06-282017-06-301  
43CK1176774610767746Short Term (27-)2017-10-042017-10-092  
44CK1176774610767746Short Term (27-)2018-03-292018-04-033  
45CK1176774610767746Short Term (27-)2018-10-222018-10-234  
46CK1176774610767746Long Term (28+)2019-04-152019-09-29041
47BK1176791110767900Short Term (27-)2015-12-162015-12-171  
48BK1176791110767900Short Term (27-)2016-03-072016-03-112  
49BK1176791110767900Long Term (28+)2018-03-062019-03-17020
50GP1176787510767875Short Term (27-)2015-01-052015-01-051  
51GP1176787510767875Short Term (27-)2016-06-082016-06-082  
52GP1176787510767875Short Term (27-)2016-09-192016-09-223  
53GP1176787510767875Long Term (28+)2017-07-172017-09-03031
54GP1176787510767875Short Term (27-)2018-12-032018-12-031  
55SS1176771110767700Short Term (27-)2018-06-042018-06-201  
56SS1176771110767700Short Term (27-)2019-09-232019-09-242  
57TS1176774110767741Short Term (27-)2018-12-032018-12-041  
58TS1176774110767741Long Term (28+)2019-01-212019-02-17011
Sheet1
Cell Formulas
RangeFormula
P2:P58P2=IF(A2<>A1,IF(E2="Short Term (27-)",1,0),IF(E2="Short Term (27-)",1+P1,0))
Q2:Q58Q2=IF(AND(P2=0,A2=A1,B2=B1),P1,"")
R2:R58R2=IF(Q2<>"",COUNTIF(OFFSET(O2,-Q2,0,Q2,1),">="&N2-365),"")
 
Upvote 0
Thanks for this J.Ty. much appreciated. I’m not able to access excel other than on a phone until Monday but this looks like it fits the bill perfectly!
The data is all mocked up, the names aren’t real, neither are the employee numbers but thank you for the consideration.
H
 
Upvote 0
No worries, write here if you have any further questions.

J.Ty.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
291
Tetra201
Tetra201
M
  • Question Question
Formula for unpaid leave adjustment
Replies
2
Views
119
Joe4
Joe4
wizau
  • Solved
Nested IF statements, or a better way?
Replies
6
Views
424
wizau
wizau
H
  • Question Question
Monitoring sick pay over a rolling 12 month period - with two levels of pay
Replies
4
Views
3K
Anonymous1378
A
N
  • Question Question
2-step Dynamic Ranges and Charts
Replies
0
Views
1K
Nick Loughton
N

Forum statistics

Threads
1,224,884
Messages
6,181,552
Members
453,052
Latest member
ezzat

Share this page

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