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

Liverlee

New Member
Joined
Nov 8, 2018
Messages
33
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.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
This might give you some assistance
Excel Workbook
AB
1starting date1/09/2018
2
3No weeks
41/11/20188.71
5ma 
62/10/20184.43
710/10/20185.57
825/10/20187.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

New Member
Joined
Nov 8, 2018
Messages
33
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
AND(ISNUMBER(A4),DATEDIF(TODAY(),A4,"d")<42)
AND(ISNUMBER(A4),DATEDIF(TODAY(),A4,"d")>=42,DATEDIF(TODAY(),A4,"d<=56)
AND(ISNUMBER(A4),DATEDIF(TODAY(),A4,"d")>56)

but not quite getting it right



<colgroup><col></colgroup><tbody>
</tbody>
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
This might give you further thought
Excel Workbook
AB
1today11/11/2018
2
3No days passed
41/11/201810
5ma 
611/09/201861
710/10/201832
828/09/201844
9ma 
1010/10/201832
111/09/201871
1229/09/201843
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,879
Members
414,413
Latest member
Sinbin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top