Formating

simonnimbus

New Member
Joined
Mar 22, 2004
Messages
4
Can anyone please help - I have a column of dates and need to be able to colour the cells of dates that are more than either one or two years from the current date (obviously current date will change).
In a similar way I need different coloured cells for dates in the future that will then automatically change when they become current date and older.

I am slightly familiar with conditional formating but not that much!!

Many thanks in advance

Simon
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
If I understand you correctly, try the conditional formatting below:
Book1
ABCD
1DateCurrDate
21/1/033/22/04
31/1/04HighlightA2:A7andConditionalFormat:
41/1/051)Cellvalue-greaterthan$D$2*730
51/1/06Formatbluebackground
61/1/202)Cellvalue-greaterthan$D$2*365
71/1/07Formatyellowbackground
Sheet1
 

simonnimbus

New Member
Joined
Mar 22, 2004
Messages
4
mmm, thanks but I'm not sure I get you. I did the conditional formating on the cells in my table and nothing happens.
Imagine you have 1000 pieces of electronic medical equipment that all need to be electrically safety testing at certain time intervals.
I have a long list of the model numbers, serial numbers etc etc and a last tested date - it is this date that I want to be shown in red to tell me that is is older than one or two years depending on the equipment.
When the equipment is then tested it will be given a date in the future and this cell should be green - until of course it reaches the cut ff date and it wil lturn red

Hope this makes sense

Simon
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Simon, I'm not quite sure how you want to set up your spreadsheet. Rather than my guessing at your layout, why don't you download Colo's Utility (link at the bottom of any page in MrExcel) and use it to post an example of your spreadsheet, including past due, not yet due dates and future dates, and use regular formatting to set the colors to the way they should be. Then let's have a go at it.

Barry
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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