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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi h. h.

Try this formula in the conditional fomatting of C9

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

HTH
PGC
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613

ADVERTISEMENT

My apologies, I had a typo in my formula.

Thanks for your help

h.h.
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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
 

Forum statistics

Threads
1,141,137
Messages
5,704,490
Members
421,353
Latest member
jekoxien15

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
Top