Conditional Formatting for Date Comparisons

[TerryMcGinnes]

I have a table where each row is a specific Individual ID Number, and each column corresponds to different dates a test was taken for that ID Number. The ID Numbers are in Column B, and the test dates go from Column H to Column AL. Column H is the baseline test, then every test after that is supposed to be taken 9 weeks (+/- 7 days) from the previous test. So Post Baseline Test 2 should be taken 9 weeks (+/-7 days) from Post Baseline Test 1, etc. I need to check this for all test dates for every ID Number. Also, not every ID Number took the same number of tests. Some only took the baseline test, some took 17 tests, some took 5, etc. I know how to compare one date to another for one ID Number, but I do not now how to apply conditional formatting to test this for the whole table so that all out of window tests in the whole table highlight. Note: I can not use macros. Can anyone provide some assistance?

 

[etaf]

i assume you need to know if less than 9weeks - 7days , as well as over 9weeks + 7days
9 weeks = 63 days

Or all all the dates based on the baseline
so 63+7 , then 126+7 , then 189+7
OR is it 63+7 from the previous date - regardless if that was out of date -
1/1/22 - then next is due 5/3/22 - Lets say it was taken 1/1/23 - so a year late ... does that make them all wrong or would the next date be due 5/3/23 and only the 1/1/23 is highlighted

 

[TerryMcGinnes]

Excluding baseline dates (since that is the first date in the series for every ID) I need to compare every subsequent date to the previous date and highlight the date if it is out of window. Every date should be compared only against the previous, so even if a date is 1 year out of window, the next date then needs to be 9 weeks (+/-7 days) from the date that was 1 year out of window. Essentially, each date comparison is a standalone check, but I am trying to find some way to apply conditional formatting so it does this for the whole table, rather than me writing 100s of individual formulas to compare two dates, then the next two, then the next two...

 

[etaf]

so you need to compare the number of days , since last date
make sure its > = 56 and less than or = 70
63 days - 9weeks + 7 days (70) -7 days( 56)
and ignore blanks

so
=AND(OR((E2-D2)<56, (E2-D2)>70),E2<>"")
should do it

i chnage some dates to make sure out of spec


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
E2:H100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(OR((E2-D2)<56, (E2-D2)>70),E2<>"")

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

I have put the formula - just for indication purposes into cells E8 ; H11 - so you can see logic - not used

Then i have counted the days - between dates - again not used - just to check if in range 56 to 70 in range E14 to H17

Book5
	A	B	C	D	E	F	G	H
1		ID	baseline
2				10/4/23
3				9/28/23	12/5/23
4				9/5/22	11/23/22	1/30/23	4/4/23	7/6/23
5				11/21/22	1/30/23	5/3/23
6
7
8					FALSE	FALSE	FALSE	FALSE
9					FALSE	FALSE	FALSE	FALSE
10					TRUE	FALSE	FALSE	TRUE
11					FALSE	TRUE	FALSE	FALSE
12
13
14
15					68
16					79	68	64	93
17					70	93
Sheet1

Cell Formulas
Range	Formula
E8:H11	E8	=AND(OR((E2-D2)<56, (E2-D2)>70),E2<>"")
E14:H17	E14	=IF(E2<>"",E2-D2,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:K5	Expression	=AND(OR((E2-D2)<56, (E2-D2)>70),E2<>"")	text	NO

 

[TerryMcGinnes]

Thank you so much! This worked perfectly, and was much more straightforward that I was expecting.

 

[etaf]

you are welcome