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.

 

[barry houdini]

OK try changing to

=AND(C9>0,DATEDIF(DATE(0,MONTH(G5),DAY(G5)),TODAY(),"ym")=0)

 

[pbt]

Thank you Very Much, Barry

That did it. This thing did not let me sleep last night

I have been up since 3:00 this morning searching for DATEDIF Not realizing it was short for Date Difference. (I was lookin for DATED and IF, duh)

Was just reading Chip Parsons site http://www.cpearson.com/excel/datedif.htmn
and trying to work it out when I received your post.

Not that I understand it all yet, but it works and I will study it to figure out what it is doing.

Thanks again,
Harry

 

[barry houdini]

I started off thinking that you might want to format a future date, if they're always past dates you only need

=(C9>0)*(DATEDIF(G5,TODAY(),"ym")=0)

note: this highlights dates for a calendar month whereas, if you want 30 days switch to

=(C9>0)*(DATEDIF(G5,TODAY(),"yd")<=30)

 

[pbt]

What I am trying to do is highlight an employees vacation hours 30 days before his anniversary date (month and day) to indicate that he will lose it if not taken. This is why I needed it to not look at the Year.

This formula also works

=(C9>0)*(DATEDIF(G5,TODAY(),"yd")<=30)

Thanks again, Barry
Harry