conditional formatting help!

[excelhelpmepls]

in column H I have dates when someone tested positive for covid and in column I I have dates when we collected blood samples from those patients. I need to highlight the dates in column I that are 5 days before the positive test in cloumn H or 5 days after the positive test date in column H.

I tried to conditionally format using a the following formula:
=($H:$H+5)>$I:$I>($H:$H-5)

but no cells were highlighted...is there something I am doing wrong? I made sure all cells were formatted as dates in the same way.

 

[gordsky]

maybe something like

=OR(I1=H1+5,I1=H1-5)

 

[JamesCanale]

Not 100% clear on what you want.

This highlights all blood tests that are more than 5 days before the test result or more than 5 days after the test result.

MrExcelPlayground7.xlsx
	H	I
1	Covid	Bloodtest
2	1/3/2022	1/5/2022
3	1/5/2022	1/3/2022
4	1/8/2022	1/2/2022
5	1/11/2022	1/17/2022
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I5	Expression	=ABS(I2-H2)>5	text	NO

 

[excelhelpmepls]

@gordsky @JamesCanale thanks for your responses! I want to highlight dates in column I that fall between 5 days before the postive test and 5 days after the test. So any date within those 10 days should be highlighted

 

[JamesCanale]

This then:

MrExcelPlayground7.xlsx
	H	I
1	Covid	Bloodtest
2	1/3/2022	1/5/2022
3	1/5/2022	1/3/2022
4	1/8/2022	1/2/2022
5	1/11/2022	1/17/2022
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I5	Expression	=ABS(I2-H2)<=5	text	NO

 

[gordsky]

@gordsky @JamesCanale thanks for your responses! I want to highlight dates in column I that fall between 5 days before the postive test and 5 days after the test. So any date within those 10 days should be highlighted

my original code will do that

 

[excelhelpmepls]

my original code will do that

I think this is only highlighting cells with dates that are either exactly 5 days before or after. Is there a way to highlight any date that is within 5 days before or 5 days after the covid+ test?

 

[gordsky]

I think this is only highlighting cells with dates that are either exactly 5 days before or after. Is there a way to highlight any date that is within 5 days before or 5 days after the covid+ test?

yes it will as that was your request in post#1. If you want a formula to highlight something between the two ranges of 5 days either side then try this

=AND(I1<>"",I1<H1+5,I1>H1-5)