Conditional formatting dates

ThedoreHolley

Board Regular
Joined
Sep 26, 2016
Messages
51
Office Version
  1. 365
Platform
  1. Windows
I am trying to conditional format a column that has dates. (D-mmm-yy).
I want to highlight dates that are 18 months -23 months amber. And anything 24 or older red.
 

Attachments

  • 1000004821.jpg
    1000004821.jpg
    127 KB · Views: 10
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
format should not matter unless text - in which would not be amber

the format is how a date looks - Excel just uses a number and fraction for time

right click and reformat as general and see if a number appears

i did ask 1st post
are they real dates

or as also suggested - share the file

change format to d-mmm-yy

Cell Formulas
RangeFormula
A3:A28A3=A2-50
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A32Expression=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))textYES
A2:A29Expression=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))textYES


Formatted as general
condfrmt-etaf.xlsx
A
1DATES
244815
344765
444715
544665
644615
744565
844515
944465
1044415
1144365
1244315
1344265
1444215
1544165
1644115
1744065
1844015
1943965
2043915
2143865
2243815
2343765
2443715
2543665
2643615
2743565
2843515
29
30
31
Sheet1
Cell Formulas
RangeFormula
A3:A28A3=A2-50
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A32Expression=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))textYES
A2:A29Expression=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))textYES
 
Upvote 0
format should not matter unless text - in which would not be amber

the format is how a date looks - Excel just uses a number and fraction for time

right click and reformat as general and see if a number appears

i did ask 1st post


or as also suggested - share the file

change format to d-mmm-yy

Cell Formulas
RangeFormula
A3:A28A3=A2-50
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A32Expression=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))textYES
A2:A29Expression=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))textYES


Formatted as general
condfrmt-etaf.xlsx
A
1DATES
244815
344765
444715
544665
644615
744565
844515
944465
1044415
1144365
1244315
1344265
1444215
1544165
1644115
1744065
1844015
1943965
2043915
2143865
2243815
2343765
2443715
2543665
2643615
2743565
2843515
29
30
31
Sheet1
Cell Formulas
RangeFormula
A3:A28A3=A2-50
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A32Expression=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))textYES
A2:A29Expression=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))textYES
I've tried multiple times and every time I add the amber it changes the RED as well.
 
Upvote 0
i guess , as mentioned, best if i can see the spreadsheet - are you able to drop onto a share - does it have confidential info in ? if so maybe blank that out
 
Upvote 0
i guess , as mentioned, best if i can see the spreadsheet - are you able to drop onto a share - does it have confidential info in ? if so maybe blank that out
It has a lot of sensitive information on it that is why I just sent the picture of the column.
 
Upvote 0
ok

so if you change the format of the column to general
are they all numbers - as i have shown

can you perhaps screen shot the conditional formatting rules - as i have done - in post 4

did you try the new amber formula, i posted earlier in post 9 yesterday

=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

did you use the sample i posted on the share to se eif that worked on your excel version and looks the same
 
Upvote 1
Solution
ok

so if you change the format of the column to general
are they all numbers - as i have shown

can you perhaps screen shot the conditional formatting rules - as i have done - in post 4

did you try the new amber formula, i posted earlier in post 9 yesterday

=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

did you use the sample i posted on the share to se eif that worked on your excel version and looks the same
I think it was more user error on my part. It us working now with the updated formula. Thank you for your patience and support.
 
Upvote 0
you are welcome, if working ok with updated formula , then i suspect the order in conditional formatting was not as shown OR STOP IF TRUE not ticked for both

make sure you decide where to put the = sign , also as mentioned in post

<2yrs and >2yrs alone will miss out EXACTLY 2 years date - hence the = , just needs to be in the colour you want
 
Upvote 0
you are welcome, if working ok with updated formula , then i suspect the order in conditional formatting was not as shown OR STOP IF TRUE not ticked for both

make sure you decide where to put the = sign , also as mentioned in post

<2yrs and >2yrs alone will miss out EXACTLY 2 years date - hence the = , just needs to be in the colour you want
Got it. Thanks
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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
Back
Top