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!

 

[Oddball2020]

Try using DATEVALUE(X4) (syntax?) in the formula to first convert the date... I'll play around and get back to you because I've done this before, but can't check atm.

 

[Oddball2020]

Try using DATEVALUE(X4) (syntax?) in the formula to first convert the date... I'll play around and get back to you because I've done this before, but can't check atm.

Sorry, mis-read your origninal post.... so you want to "roll" up the dates as the formula moves down the list?

 

[Oddball2020]

Sorry, mis-read your original post.... so you want to "roll" up the dates as the formula moves down the list?

Probably would be easier to attach a short example of your sheet, it's hard to do a formula just off your description. But generally you can also put a reference to the prior row's forumla inside the the next, so the date comparasion is building up as the formula is pulled down the list.

 

[eagerexceler]

Thanks for your reply. Yep, I'm essentially trying to see if I can roll the date comparison up in an easier way to identify if a unique number has an account with a date within a range of a date in an adjacent cell. I've attached a dummy mini-sheet. Hope this helps.

The other step I need to get to (which may require a seperate thread) is that if the date in a cell is within the specified range of 30 day of the adjacent cell date, I need to sum the value of the applicable cell in column S and roll it up into column AX.

Sorry for getting more complicated here!

Excel Nest If Date Within Another Date Example.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	AX
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?	Ad Ex
5	1	1-1	1	1	1	1/1/19	1/4/19	I	32	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	18089.4	1	b	a	1/1/2019																									#VALUE!	#VALUE!
6	2	2-1	1	2	2	1/1/19	1/4/19	I	391	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	11151.2	1	b	a	1/1/2019																									#VALUE!	#VALUE!
7	3	3-1	1	3	3	1/3/19	2/15/19	I	1	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	351119	1	b	a	1/3/2019																									#VALUE!	#VALUE!
8	4	4-1	1	4	4	1/4/19	1/11/19	I	558	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	6125	1	b	a	1/4/2019																									#VALUE!	#VALUE!
9	5	5-1	1	7	5	1/11/19	1/25/19	I	668	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	13378.4	1	b	a	1/11/2019	5/31/2019	12/20/2019																							#VALUE!	#VALUE!
10	5	5-2	1	5	5	5/31/19	6/7/19	I	653	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	37537.6	1	b	a	1/11/2019	5/31/2019	12/20/2019																							#VALUE!	#VALUE!
11	5	5-3	1	6	5	12/20/19	12/23/19	I	280	x	xx	xxx	xxxx	xxxxx	xxxxxx	(blank)	0	0	11252.5	1	b	a	1/11/2019	5/31/2019	12/20/2019																							#VALUE!	#VALUE!
Sheet2

Cell Formulas
Range	Formula
W5:AU11,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:AV11	AV5	=IF((OR(($W5+AV$3>$X5),$X5+AV$3>$Y5,$Y5+AV$3>$Z5,$Z5+AV$3>$AA5,$AA5+AV$3>$AB5,$AB5+AV$3>$AC5,$AC5+AV$3>$AD5,$AD5+AV$3>$AE5,$AE5+AV$3>$AF5,$AF5+AV$3>$AG5,$AG5+AV$3>$AH5,$AH5+AV$3>$AI5,$AI5+AV$3>$AJ5,$AJ5+AV$3>$AK5,$AK5+AV$3>$AL5,$AL5+AV$3>$AM5,$AM5+AV$3>$AN5,$AN5+AV$3>$AO5,$AO5+AV$3>$AP5,$AP5+AV$3>$AQ5,$AQ5+AV$3>$AR5,$AR5+AV$3>$AS5,$AS5+AV$3>$AT5,$AT5+AV$3>$AU5)),"Present", "No Present")
AW5:AW11	AW5	=IF(OR(($W5+AW$3>$X5),$X5+AW$3>$Y5,$Y5+AW$3>$Z5,$Z5+AW$3>$AA5,$AA5+AW$3>$AB5,$AB5+AW$3>$AC5,$AC5+AW$3>$AD5,$AD5+AW$3>$AE5,$AE5+AW$3>$AF5,$AF5+AW$3>$AG5,$AG5+AW$3>$AH5,$AH5+AW$3>$AI5,$AI5+AW$3>$AJ5,$AJ5+AW$3>$AK5,$AK5+AW$3>$AL5,$AL5+AW$3>$AM5,$AM5+AW$3>$AN5,$AN5+AW$3>$AO5,$AO5+AW$3>$AP5,$AP5+AW$3>$AQ5,$AQ5+AW$3>$AR5,$AR5+AW$3>$AS5,$AS5+AW$3>$AT5,$AT5+AW$3>$AU5),"Present", "Not Present")

 

[Oddball2020]

Thanks for your reply. Yep, I'm essentially trying to see if I can roll the date comparison up in an easier way to identify if a unique number has an account with a date within a range of a date in an adjacent cell. I've attached a dummy mini-sheet. Hope this helps.

The other step I need to get to (which may require a seperate thread) is that if the date in a cell is within the specified range of 30 day of the adjacent cell date, I need to sum the value of the applicable cell in column S and roll it up into column AX.

Sorry for getting more complicated here!

Thanks this helps, I'll try it out on my end, but it'll be a few hours before I can get back to you.

 

[Oddball2020]

Ok, so looking this over now in excel, it makes better sense what you were trying to explain, thanks for sharing the data/formulas. There's a few ways to go about this, but honestly I probably would have done something similar (and have so) in my time with a messy nested formula. So here's the options I can think of, learning from my headache

1) Keep the insanely nested formula like you did, but either add a AND to account for the next right cell <>"" or just shroud the whole formula in IFERROR(..... , "") so the VALUE isn't glaring at the end. This really doesn't address simplifying things like you asked, so it's really just cosmetic. Also cosmetic is your "in 3" or "in 30" doesn't match AV AW between rows 3 and 4... but that's typo probably.

2) The simplest answer is this: since you're doing the same thing over that range on each row, comparing the left to right dates with the 3 or 30, but in a single cell... Why not apply just that basic comparison formula, but auto-fill drag the formula over to the right so it floats (relative reference), and does the same check over the range for each neighboring columns?. So say in AV5 insert

=IFERROR(IF(W5+$AV$3>X5,"Present", "No Present"), "")

then drag that to the right to cover all the other comparisons.

You'll end up with No Present No Present No Present Present.... or blanks across that range

Then you just add another column to COUNTIF (using present as a criteria) or MATCH (for "present" which will stop at the first encountered Present moving left to right over the range) over that horizontal range for "Present", and it'll essentially capture result that you were going for in your messy nested formula.

3) Use VBA to do the comparisons for you in a loop over the W through AU. It would actually be a double loop, first across the row, then repeat on the next row. If it finds a "Present" situation, it'll exit that row's loop and insert Present in that cell. So no nested formula needed, but a bit of coding.

If it's your sheet and you control it, it's easier just to do 2) and avoid the headache of messy formulas altogether!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=IF(SUMPRODUCT(--(IFERROR(($W5:$AT5+AV$3)>$X5:$AU5,0))),"Present","Not Present")

 

[eagerexceler]

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!!

 

[Fluff]

Have you tried my suggestion?