Please help Excel type thing...

[Rubber Bullets]

Hi,

I have just taken over the management of my departments Mandatory Training. Up to this point it has not been managed very well and I'm aiming to improve matters.

Basically there're about 30 people who have to do 6 refresher courses a year, in addition to another 4 that are done once.

I am currently putting all the info into an excel grid, name down the side and course name across the top with dates in the boxes.

What I'd really love is for Excel to display the dates in different colors according to when they are, relative to the day that the document is opened.

For instance I'd like future booked courses to be in blue, courses that have already happened, but in the last 12 months (for the annual refresher ones) in black and any out of date ones in red.

Clearly I can do this manually, but it would make things loads easier if it happened automatically.

Does anyone know if Excel is capable of this? Or Access? Or anything else for that matter. And if so how do I do it?

Many thanks

RB

 

[anattic]

Excel can do this using "Conditional Formatting". There's a good bit of help here:

http://office.microsoft.com/en-us/assistance/HA011116611033.aspx

In short, select the row/column with the dates in, and select "Conditional Formatting" from the "Format" menu. Create 2 conditions: "Cell Value Is less than =NOW()", and "Cell Value Is greater than or equal to =NOW()". Then setup the formatting to your taste.

 

[Rubber Bullets]

Many thanks Anattic, I'll give it a try.

RB

 

[Jupitus]

Yep - that works for up to 3 scenarios... more than 3 and it gets a fair bit harder, but not impossible...


I put a function "=today()" in one cell, then "=today()-365" in another and referenced these in the formatting dialog box:

 

[Rubber Bullets]

Thanks Jupitus, that looks perfect.

I've played around a bit and got it working perfectly.

Thanks to both of you.

RB