Conditional Format Formula help

[pbt]

I can't seem to get this one.

vacation tracking LOG IWS 2006 TEST 1.xls
	C	D	E	F	G
1		9/14/06
2
3
4		4/20/06
5	20-Apr-06	20-May-06	20-Jun-06	20-Jul-06	20-Aug-06
6					120
7					8
8
9	112
7 YEAR EMPLOYEES

I am trying to Highlight (color) C9 if greater than 0, and only when Todays date is 30 days or less before date in G5.

I tried what I could think of but can't come up with something with the two criteria.

Thanks
h.h.

 

[pgc01]

Hi h. h.

Try this formula in the conditional fomatting of C9

=AND(C9>0,TODAY()>=G5-30)

HTH
PGC

 

[pbt]

Thanks for the quick reply, pgc101

It does highlight the cell, but no matter what date I have in G5 it remains highlighted.

Better explanation ~ Need it to highlight only if Todays date is 30 days or less before date entered in G5. If it is 31 or greater then no highlight.

Thanks,
h.h.

 

[pgc01]

Hi again

I checked and it works for me.
My guess is that in G5 you don't have a date, but a text with a date format.

Didn't you by accident format G5 as text, or inserted the date as text?

If you want to check if G5 is text write in a cell =ISTEXT(G5). You should get FALSE.

Please do the following test: Format G5 as General and insert any date in December 2006. C9 should not be highlighted.


Hope it helps
PGC

 

[pbt]

My apologies, I had a typo in my formula.

Thanks for your help

h.h.

 

[pbt]

Need a little fine tuning.

How can I get this formula in my Conditional Format to look at only the Month and Day ignoring the Year.

=AND(C9>0,TODAY()>=G5-30)

I have tried

=AND(C9>0,MONTH>=G5-1,DAY>=G5-30) But this does not work.

The reason I need it to ignore the Year is, I have some other cells that the date will be anywhere from 1990 to 2005

Thanks
h.h.

 

[Scott Huish]

To determine the difference of days between dates, you need a complete date to do the calculation. While, its possible to modify the year in the calculation, there is no way to accurately determine if any given month is before or after another without the year. It is the year that determines this. For example if the date in G5 was December 15, 2005, and the current date is January, 15, 2006, December is before it, but if the current date was January 15, 2005, it is after. Any month can do this, it all depends on the year.

 

[pbt]

Thanks for the explanation, Hotpepper.

It now has made me rethink what I'm trying to do.

Maybe I should start a new thread with this next question (let me know if I should) If it is possible then I can stick with the CF above.

Is it possible to take dates like 7/26/2005, 12/3/2001, etc. and change them all to current year leaving the month and day as is? Or does this also run into your explanation above.

All dates are in one column.

I was thinking of an IF formula but can't come up with anything. VBA is definately an option too.

Thanks
h.h.

 

[barry houdini]

OK, can I clarify, harry?

If today is 16th September you want to format C9 when C9 itself is bigger than zero and the date in G5 is somewhere between 16th September and 16th October - no matter what year

If so, try this formula within conditional formatting

=AND(C9>0,DATEDIF(DATE(0,MONTH(TODAY()),DAY(TODAY())),G5,"yd")<=30)

 

[pbt]

Thanks Barry,

That is so close to what I am looking for:

If today is 16th September you want to format C9 when C9 itself is bigger than zero and the date in G5 is somewhere between 16th September and 16th October - no matter what year

one change though, I need ~ somewhere between 16th August and 16th September

I tried changing <=30 to >=30 also to <=-30 and >=-30

None work. Don' know where formula needs to be changed.

harry