Making dates live on Excel.

[bibacux2001]

I am trying to devise a spreadsheeet on excel that is essentially a training matrix. Box A1 shall have an initial date that can be changed but depending on the relative dates across the training module if a training certificate fall byond a certain perion in relation to the date in A1 then how do you make that cell change colour... For example. lets say the date in the box is todays date 4th october 2006. then all training certificates that are within 3 months of this date ie January 4th 2007. will be green but anything before Jan 4th would be red so as to alert the operator that a certificat needs to be flagged for updating. If someone could point me in the right direction I would appreciate it.. thanks.

[Machecoul]

Format - Conditional Formatting

If cell value - is less than 04/10/06 .... click format and make red
add another
If cell value - is greater than 05/10/06 .... click format and make green

This worked when I tried it. (Didn't realise it worked with dates - so should work for you)

Make cell bold to show up better as well.

Have a play.

[bibacux2001]

Thanks for that Machecoul. but how do I make the date live ie it chages day to day... is that possible. For eg everytime I go onto spreadsheet it will give the date of that day and all the relevant cells will change. so one day a cell may be green however the next day when i open spreadsheet it will be red as a time boundary has passed....is this too complicated...

[Machecoul]

It might be possible - I tried INSERT - FUNCTION and selected TODAY - That put todays date in a cell. (Say A1)
In A2 I put =A1+20
In A3 i put =A2+20
This added 20 days to each following date. I assume that would change each day based on what goes into A1. (Can't check until tomorrow!!!)

Then using conditional formatting as above you can use something like ... is greater than or is less than ... and in the last box put ... A1+20 and as above change colour to red or green etc. You might have to do this for every column.

Looks like it might work ????!!!!

Just read question again and you might not need to use =A1+20 in A2 but fix the date you need e.g. 04/10/06 and then continue with =A2+20

[kempie]

The date in A1 would be defined using the formula
=TODAY()

and the formulae used in the conditional formatting would be in the style
=$A$1+91

where the 91 approximates to the 3 month limit you described.

[Machecoul]

Yes, Have just discovered there is no easy way of adding on 3 months

[bibacux2001]

thanks guys will give this a whizz .. thanks for your effort..