Conditional formatting for number of weeks passed

[CaptainGravyBum]

Hello,
I need to add conditional formatting for column D on my sheet when the date entered was ten weeks prior to the current date. Columns A:C are slightly different in that the highlight needs to happen when the date shown is three months away or less.
There are many posts about conditional formatting, but I can't get them working correctly, not sure if it's because they are day based or the formula I've been using is just formatted wrong.
I've ditched the formula's in the sheet for now, so it's a clean slate if anyone can help.
Thanks.

Book5.xlsx
	A	B	C	D
1	15.12.2027	15.12.2027	19.12.2027	03.01.2024
2	01.03.2028	03.03.2028	10.02.2028	03.01.2025
3	09.03.2027	10.03.2027	29.03.2027	03.01.2025
Sheet1

 

[CaptainGravyBum]

Such an idiot! Just realised the date formatting is incorrect. If I change the . to / it works fine.

 

[etaf]

edit = posted at same time

those are not dates - BUT text
whch maybe the reason you have an issue

 

[CaptainGravyBum]

I've managed to get a bit further along now the formatting is correct, but it still isn't correct.
D1 has highlighted but it isn't 10 weeks from now and I will need dates that are already passed to be in red. I'm not entirely sure what A:C are doing, they should highlight when todays date is 3 months or less from the date provided and if the date shown has already passed they need to be in red.
Sorry to dump all this over, no doubt the solution is obvious, I just can't see it.
I can't post my sheet here, I'll try again in another reply.

 

[CaptainGravyBum]

Nope, won't let me paste it in using the xl2bb now.

 

[etaf]

10 weeks Prior is that in future
so that 10*7 = 70days


what column are you comparing the date ON - or all 4

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

Highlight applicable range >>
A1:D10000 - 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(A1 <> "" , A1<TODAY())

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

Thats RED

Next is less than 3months

=AND(A1 <> "" , A1<date(Year(TODAY()),Month(today()),day(today())+70)

ensure the 1rst rule RED is at the TOP of the LIST and stop if true
otherwise you need another AND condition

=AND(A1 <> "" , A1<date(Year(TODAY()),Month(today()),day(today())+70), A1>=today() )

Book1
	A	B	C	D
1	12/15/27	12/15/27	12/19/27	1/3/24
2	3/1/28	3/3/28	2/10/28	1/3/25
3	3/9/27	3/10/27	3/29/27	1/3/25
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:D3	Expression	=AND(A1 <> "", A1<TODAY())	text	YES
A1:D3	Expression	=AND(A1 <> "", A1<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+70), A1>=TODAY() )	text	YES

 

[CaptainGravyBum]

Hi @etaf
That works great, thanks!
Took me a while to realise that 10 weeks and 3 months away is pretty much the same thing .

 

[etaf]

you are welcome
if you want 3 months rather than 70days, then use +3 on the months

=AND(A1 <> "", A1<DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())), A1>=TODAY() )