Newbie to 2010 excel need help!

[cmlloveless]

I need to create some conitional formats, but don't know how. I am used to excel 2007 edition (wasn't a pro by any means) and dont have a clue how to do things on 2010.

I have a spread sheat that tracks annual training and competency for the staff at my work. There is training that is due annually and some due every 2 years. What I am looking to have happen is that I want the cell to turn yellow 30 days before the due date and red when it is expired. Any idea how to set this up? What I will have is the date that the current training was done on.

I.E. Bob did his training on 04/25/2010 so on 03/25/2011 the cell would turn yellow and 04/26/2011 the cell would turn red unless I put in a new date.

Any help would be appreciated!

 

[GlennUK]

I was under the impression that Conditional Formatting under 2010 was almost identical to Excel 2007, except for approx 3 new features ( reference another sheet, improved data bars, and improved icon lists ).

Unless I've been misled of course ... I do not have Excel 2010 myself yet, so I can't imagine what you are getting stuck on.

 

[Ruddles]

Is it the actual formulae that you're struggling with?

If you have a date in A1 and you want to test if it's less than 30 days away, the formula would be:-

=and[COLOR=red][B]([/B][/COLOR]a1>0,a1< [COLOR=blue][B]([/B][/COLOR]today()+30[B][COLOR=blue])[/COLOR][COLOR=red])[/COLOR][/B]

To test if the date has passed:-

=and[COLOR=red][B]([/B][/COLOR]a1>0,a1< today()<TODAY()[B][COLOR=red])[/COLOR][/B]

(I inserted spaces after the < symbols to stop the forum software throwing a wobbly: you would leave them out.)

 

[cmlloveless]

Well when I did it on 2007 I used a formula that was similar to this:

=IF(today+365.... I don't remember it exactly.

 

[cmlloveless]

Is it the actual formulae that you're struggling with?

If you have a date in A1 and you want to test if it's less than 30 days away, the formula would be:-

=and[COLOR=red][B]([/B][/COLOR]a1>0,a1< [COLOR=blue][B]([/B][/COLOR]today()+30[B][COLOR=blue])[/COLOR][COLOR=red])[/COLOR][/B]

To test if the date has passed:-

=and[COLOR=red][B]([/B][/COLOR]a1>0,a1< today()<TODAY()[B][COLOR=red])[/COLOR][/B]

(I inserted spaces after the < symbols to stop the forum software throwing a wobbly: you would leave them out.)

I tried this formula and it does not give me what I want. It is showing me that that it is within 30 days of the date I put in. What I need is a formula the shows me yellow if it is 335 days past the date I have entered and then red if it 365 days past the date I have entered.

Any ideas on a formula for that?

 

[Ruddles]

=and(a1>0,a1+335<today())

Test with a range of dates to verify it's correct.

 

[cmlloveless]

Ok so I lied, I guess I'm using 2007 excel and the last one my work has was 2003. So it is very different to me.

=and(a1>0,a1+335)<TODAY())< pre>

So if I type in this formula under conditional formating (assuming I change the "a1" to the correct cell/cells It will do what Im wanting it to?

 

[Ruddles]

Oops, **** those greater than symbols!

It should be:-

=AND(A1>0,A1+335< TODAY())

or:-

=AND(A1>0,A1< TODAY()-335)

whichever you find easiest to read.

Yes, change A1 to the cell you want to test.

 

[cmlloveless]

Awsome that works! Now how do I include mutliple cells within one formula?

I.E. I want that formula to work on the entire R columb or the entire 4th row?

 

[Ruddles]

Copy the cell and Paste Special > Formats or use the Format Painter.