Conditional Format Formula help

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
I can't seem to get this one.
vacation tracking LOG IWS 2006 TEST 1.xls
CDEFG
19/14/06
2
3
44/20/06
520-Apr-0620-May-0620-Jun-0620-Jul-0620-Aug-06
6   120
78
8
9112
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi h. h.

Try this formula in the conditional fomatting of C9

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

HTH
PGC
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
My apologies, I had a typo in my formula.

Thanks for your help

h.h.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top