Formula To Identify If Cell Dates Occur Within 30 Days of Adjacent Cell Date

[eagerexceler]

Hello y'all,

I've got a simple formula to identify if a date in W4 occurs within 30 days of a date within X4: =IF(W4+30>X4,"Readmission Present", "No Readmission").

I'm trying to expand the formula to have it include this date comparison for the next 24 rows. As in, also including IF(X4+30>Y4.... and so on, all the way to AU4. I know I can do this with a crazy nested formula, but I'm wondering if anyone has any insight into making this a simpler formula.

Thank you!

 

[eagerexceler]

Hi Fluff, I have tried your suggestion but I can't seem to get it to work properly. I'll be playing with it more when I get more availability. Thank you.

 

[Fluff]

I've changed a couple of the dates to show the result

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
1
2
3																							1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	3	30
4	#	#-1	Provider	#2	#	Beg	End	Ind	Calc	Name	Tot1	Tot2	Act	Amt	Cat	SL	Re	CH	Ex	CY19	Mar	Pro	Ad 1	Ad 2	Ad 3	Ad 4	Ad 5	Ad 6	Ad 7	Ad 8	Ad 9	Ad 10	Ad 11	Ad 12	Ad 13	Ad 14	Ad 15	Ad 16	Ad 17	Ad 18	Ad 19	Ad 20	Ad 21	Ad 22	Ad 23	Ad 24	Ad 25	W/In 30?	W/In 3?
5	1	1-1	1	1	1	01/01/2019	04/01/2019	I	32	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	18089.42	1	b	a	01/01/2019																									Not Present	Not Present
6	2	2-1	1	2	2	01/01/2019	04/01/2019	I	391	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	11151.21	1	b	a	01/01/2019																									Not Present	Not Present
7	3	3-1	1	3	3	03/01/2019	15/02/2019	I	1	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	351119.2	1	b	a	03/01/2019																									Not Present	Not Present
8	4	4-1	1	4	4	04/01/2019	11/01/2019	I	558	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	6125	1	b	a	04/01/2019																									Not Present	Not Present
9	5	5-1	1	7	5	11/01/2019	25/01/2019	I	668	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	13378.38	1	b	a	11/01/2019	12/01/2019	20/12/2019																							Present	Present
10	5	5-2	1	5	5	31/05/2019	07/06/2019	I	653	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	37537.64	1	b	a	11/01/2019	31/05/2019	28/06/2019																							Not Present	Present
11	5	5-3	1	6	5	20/12/2019	23/12/2019	I	280	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	11252.47	1	b	a	11/01/2019	31/05/2019	20/12/2019																							Not Present	Not Present
Main

Cell Formulas
Range	Formula
W11:AU11,W10:X10,Y5:Y9,W5:W9,X5:X8,Z5:AU10,W2:AU2	W2	=IFNA(IF($H2="I",VLOOKUP($A2&"-"&W$3,$B:$F,5,FALSE),""),"")
X3:AU3	X3	=W3+1
A5:A11	A5	=E5
B5:B11	B5	=IF($H5="I",$E5&"-"&COUNTIFS($E$5:$E5,$E5,$H$5:$H5,"I"),"OP")
AV5:AW11	AV5	=IF(SUMPRODUCT(--(IFERROR(($W5:$AT5+AV$3)>$X5:$AU5,0))),"Present","Not Present")

 

[Oddball2020]

Thanks Oddball, I appreciate your details in looking at this! I'm definitely still a beginner, so I'm getting to learn more here.

1. So the problem I'm running into with the crazy nested formula is that the formula isn't working after the comparison of column Z903 to column AA903 in a particular example that should work. Is this because there isn't anything populated in cell AA903? What could I use to fix this? Sorry, I didn't initially include this example in my dummy worksheet.

2. I like that this simplifies things, but I ultimately need to compare these dates using two date amounts (3 vs 30), so wouldn't this require me to add a bunch more columns?

3. I'm not quite able to do much VBA yet, so this might be too much of a headache for me right now.

Again, thank you for your help!!

I agree with Fluff, try his single formula first, using it both in the 3 and 30 columns (may need to tweak so it's looking at the 3 and 30 correctly). If that wasn't an option for whatever reason, then yes you could splinter out another section for 30 and hide all those extra columns so they're not an eyesore on your sheet, just keeping the final tally columns viewable. He's formula appears to work though, I tried it on my end.

 

[eagerexceler]

Hey y'all, I got this formula working thanks to your help.

I have another formula in this spreadsheet that I can't crack, wondering if y'all can provide more help. Let me know if I need to start a new thread.

So in my spreadsheet each row represents a unique account number in column D, with a unique amount in column S. If a particular account is labeled as "Present" in, for example AV9, I need to have the corresponding amount from S9 populate in AX9. The end goal is to have accounts with the "Present" designation tally an amount per row that can then by Pivoted in a table. My attempts have been to do an index match type of nested formula, but I can't come up with it.

Hope I explained well. Thanks.

 

[Fluff]

How about

=if(OR(AV9="Present",AW9="Present"),S9,""))

 

[eagerexceler]

Y'all are killing it for me! Good lessen for me to not always overthink things.

Very much appreciated.

 

[Fluff]

My pleasure

 

[eagerexceler]

Ok guys, I hope you don't hate me for this, but I have another formula I'm hoping to come up with.

I added three columns to identify similar information. However, in columns AY and AZ I only need to identify if the common identifier in column A has any unique account numbers from column D that occur within 3 or 30 days of the date in column F. So this formula would be similar, but really only comparing dates for the common identifier in column A and the corresponding date in column F to identify "Oth Present" or "Oth Not Present". I can then use the formula you already came up with to get my result for column BA.

I'm sorry for throwing more at y'all!

Excel Nest If Date Within Another Date Example.xlsm
	AY	AZ	BA
3	30	3
4	Oth W/In 30?	Oth W/In 3?	Oth Exp
5
6
7
8
9
10
11
Sheet2

 

[Oddball2020]

Ok guys, I hope you don't hate me for this, but I have another formula I'm hoping to come up with.

I added three columns to identify similar information. However, in columns AY and AZ I only need to identify if the common identifier in column A has any unique account numbers from column D that occur within 3 or 30 days of the date in column F. So this formula would be similar, but really only comparing dates for the common identifier in column A and the corresponding date in column F to identify "Oth Present" or "Oth Not Present". I can then use the formula you already came up with to get my result for column BA.

I'm sorry for throwing more at y'all!

Excel Nest If Date Within Another Date Example.xlsm
	AY	AZ	BA
3	30	3
4	Oth W/In 30?	Oth W/In 3?	Oth Exp
5
6
7
8
9
10
11
Sheet2

Though related to the original sheet, if it's unrelated to your original issue that appears solved, I think you need to post this as a new question? I'll keep an eye out for it once posted.

Did you mark Fluff's formula as solved above?

 

[eagerexceler]

Gotcha, will do. I did mark it as solved, so I will post a new thread.

Thank you.