Crafting formula

[iphamjames]

Hello,

I am trying to create a formula that will be used in a living document that is tracking 30 day readmissions by returning the most recent discharge date within 30 days. I have tried many different variations of formulas but have not been able to find one that works "automatically."

This is for a medical practice and so there are multiple episodes for many unique individuals that have an ID.

One example that I can give is that a person is discharged from a hospital on 12/15/2023 and returns to a hospital on 12/18/2023. Obviously this is a readmission with the returned value of "12/15/2023."

Unfortunately, the formulas I have tried will change the value when the person returns again.

For example:

Episode 1 of person 10: Admission- 12/10/2023 and Discharge- 12/15/2023 No Readmission because this is the fist one.

Episode 2 of person 10: Admission- 12/18/2023 and Discharge- 12/23/2023 Readmission of 12/15/2023

Episode 3 of person 10: Admission- 1/18/2024 and Discharge- 1/30/2024 Readmission of 12/23/2023

This is where my formula would change the outcome of the value for the "Readmission of 12/15/2023" to "Readmission of 12/23/2023" rather than keeping the readmission for episode 2 as 12/15/2023 and the readmission for episode 3 as 12/23/2023.

I would love some help on coming up with a formula on this. It has been very difficult for me.

Thank you!

 

[Cubist]

It would be helpful if you can post the sample data using XL2BB.

 

[etaf]

can you sort the data ??
if you sort by discharge date - then this may work
=IF(MAXIFS($C$1:C1,$A$1:A1,A2)=0,"FV",MAXIFS($C$1:C1,$A$1:A1,A2))

but other members , i'm sure will have better solutions

Book10
	A	B	C	D
1	ID	start	end
2	2		12/1/23	FV
3	2		12/5/23	12/1/23
4	3		12/10/23	FV
5	1		12/15/23	FV
6	4		12/20/23	FV
7	2		12/20/23	12/5/23
8	5		12/21/23	FV
9	1		12/23/23	12/15/23
10	2		1/1/24	12/20/23
11	2		1/1/24	1/1/24
12	3		1/5/24	12/10/23
13	3		1/5/24	1/5/24
14	4		1/9/24	12/20/23
15	4		1/9/24	1/9/24
16	5		1/13/24	12/21/23
17	5		1/13/24	1/13/24
18	1		1/30/24	12/23/23
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=IF(MAXIFS($C$1:C1,$A$1:A1,A2)=0,"FV",MAXIFS($C$1:C1,$A$1:A1,A2))

 

[JGordon11]

timers 3 lane.xlsm
	A	B	C	D	E	F	G
1	ID	In	Out	Type	LOS	30 day re-admit
2	1001	3/18/2024	3/20/2024	Inpatient	3
3	1002	7/18/2023	7/31/2023	Inpatient	14
4	1002	8/26/2023	8/30/2023	Inpatient	5	7/31/2023
5	1002	9/4/2023	9/15/2023	Inpatient	12	8/30/2023
6	1002	9/24/2023	9/27/2023	Inpatient	4	9/15/2023
7	1002	10/6/2023	10/25/2023	Inpatient	20	9/27/2023
8	1002	3/1/2024	3/25/2024	Inpatient	25
9	1003	1/1/2024	1/9/2024	Inpatient	9
10	1004	3/6/2024	3/7/2024	Observation	2
11	1005	7/8/2023	7/17/2023	Inpatient	10
12	1005	12/11/2023	12/15/2023	Inpatient	5
13	1005	12/18/2023	12/22/2023	Inpatient	5	12/15/2023
14	1005	1/11/2024	1/18/2024	Inpatient	8	12/22/2023
15	1005	2/24/2024	2/28/2024	Inpatient	5
16
Sheet5

Cell Formulas
Range	Formula
F2:F15	F2	=IF((A2=A1)*(B2-N(C1)<=30),C1,"")

 

[iphamjames]

I think the trouble it is still having is that in this example the data ID is in order. If they are jumbled, then the formula does not return the correct value.

any ideas on how this can work when the entries are randomized?