Conditional formatting w/color

airmanrobert

New Member
Joined
Feb 19, 2002
Messages
2
I maintain a large list of people and track training dates. My problem is that I need a way to conditionally format cells so that when these dates turn a year old, they highlight.
ex. Supervisor Safety - Completed 12Jan01
I need this date highlighted since it is more than a year old......

PLEASE HELP!!!! This is driving me nutz!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Let's say your list starts in cell A2; follow these steps:

(1) Select A2.
(2) Click on Format > Conditional Formatting.
(3) Under the script Condition 1, click the down arrow and select "Formula Is".
(4) In the field to the right, enter (or paste in) this formula:
=A2-DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))<=0
(5) Click the Format... button, and choose your formatting preferences.
(6) Click OK, then OK.
(7) Copy A2 and paste special for Formats as far down as you need to.

Tom Urtis
 
Upvote 0
Anon or Tom

I've tried using this with 11 months but no luck. I reduced 365.25 to 335.25 and get a "false" response.

Brian
 
Upvote 0
You guys are great!!! If you ever come to Okinawa, the beer is on me!

Got one last question... what is the .25 on the end of 365 for?

Thanks
Rob
 
Upvote 0
A year doesn't have exactly 365 days, that's why every 4 years we have a Feb 29th, it's just a way of adding the 1/4 of a day that each year has.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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