How do I color format dates in one column into another column

[Liverlee]

Hi,

New to the forum

I'm here cos i'm stuck with a task.

I have a spreadsheet with a list of dates and non dates in column A

In column B I want to color highlight cells (so that they can be overwritten with other text)

I want to show in column B

1) dates from column A that are greater than 8 weeks
2) dates from column A that are between 6 and 8 week date
3) dates from column A that are less than and upto 6 weeks.

and finally colour highlight in column b all the non dates in column A. Some are blanks and some have some text (i.e) N/A, TBC.

I've tried

=A2:A10<TODAY()

Not sure if i can use today as part of the formula if the dates in column A are not today i.e 08/11/2018.

Please, please, please somebody help me.

 

[pedro-egoli]

This might give you some assistance

Excel Workbook
	A	B
1	starting date	1/09/2018
2
3		No weeks
4	1/11/2018	8.71
5	ma
6	2/10/2018	4.43
7	10/10/2018	5.57
8	25/10/2018	7.71
Sheet1

Conditional formatting set as
Rule 1 Cell value is between 8 and 52
Rule 2 Cell value is between 7 and 8
Rule 3 Cell value is between 1 and 6
This was done on excel 2003 , rather than a colour if text or blank in column A it will be blank

 

[Liverlee]

Thank you pedro

I got the dates wrong though

They are all supposed to be before dates!

for example

Six weeks before the date in column A = red cell in column b.
Six to Eight weeks before the date in column A = amber cell in column b
More than Eight weeks before the date in column A = green cell in column b


i've tried these forumla's in column b
[TABLE="width: 522"]
<colgroup><col></colgroup><tbody>[TR]
[TD]AND(ISNUMBER(A4),DATEDIF(TODAY(),A4,"d")<42)[/TD]
[/TR]
[TR]
[TD]AND(ISNUMBER(A4),DATEDIF(TODAY(),A4,"d")>=42,DATEDIF(TODAY(),A4,"d<=56)[/TD]
[/TR]
[TR]
[TD]AND(ISNUMBER(A4),DATEDIF(TODAY(),A4,"d")>56)

but not quite getting it right


[/TD]
[/TR]
</tbody>[/TABLE]

 

[pedro-egoli]

Perhaps if you could post a copy of your spreadsheet that would assist.
See posting aids here https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[pedro-egoli]

This might give you further thought

Excel Workbook
	A	B
1	today	11/11/2018
2
3		No days passed
4	1/11/2018	10
5	ma
6	11/09/2018	61
7	10/10/2018	32
8	28/09/2018	44
9	ma
10	10/10/2018	32
11	1/09/2018	71
12	29/09/2018	43
test

Conditional formatting
Rule 1 Cell value is between 57 and 365 colour Green
Rule 2 Cell value is between 43 and 56 Colour Pink
Rule 3 Cell value is between 1 and 42 Colour Red