cell to indicate 60 months from start date

[Archie11]

Hi,

I need your help please. I will try my best to explain what i need.

At my work i had to create a spreadsheet using excel 2010 to keep a record of individuals training codes and there expiry dates. These dates will expire 60 months or 36 months of start date.

So I created a spreadsheet with a list of staff names and all they training codes. (That's the easy part).

This is where i need your help. Is they a way to run a macro or rule in excel, so I only input a start date of a training code and it will automatically highlight that cell red when it reaches 60 months of the date inputted? and the same for 36 months.

Also if it could highlight yellow 30 months before the 60 months to give a warning that the training needs to be done, and 18 months before 36 months.

Help Please!

Many thanks

Archie

[alansidman]

Look at this tutorial on conditional formatting

http://www.techonthenet.com/excel/qu...rmat4_2010.php

[Archie11]

Hi Alan,

Thank you for the reply.

I'll have a look at this.

[Archie11]

Alan,

where it has the formula =now()+30 I take is 30 days from now, but what do I need to do if I want 30months from now?

[Richard Buttrey]

Hi

Use

Use a conditional format. i.e. if the start date is in A1 then the CF for A1 is

Please Login or Register  to view this content.

for 36 months change the -61 to -37

[Archie11]

Thanks Richard,

I'll give that a go.

[Archie11]

Richard,

Again thank you for the formula, but whatever I input a date it automatically turns the cell red. What i need is the cell to be clear, just with a date and then when it reaches 30 months it turns yellow and the turns red at 60 months.

sorry if i am being dump on this..

[Richard Buttrey]

Hi,

You'll need to upload the workbook so that I can see your request in context and why what you've entered isn't working.

[Archie11]

could you have a look please.

i left a comment in cell d20

[Richard Buttrey]

Hi,

Does the attached help?
I decided in the end to use the DATEDIF() function which Microsoft for some reason known only to themselves have decided not to list it in the normal functions list.

[Archie11]

Richard,

your a genius.

Thank you

[Archie11]

Sorry Richard can I ask one thing please,

Is possibly when i put a date into the cell, the cell will turn green first? then run the formula DATEDIF() for 30 months and 60 months.


many thanks

[Richard Buttrey]

Hi,

If you mean that the default colour for a cell with a date that is less < 30 months then just add a third CF to cell D10

=D10>0

format it as green, apply it to the whole range $D$10:$K:$37, and make sure it's the third CF rule otherwise it will take precedence over the other two.