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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

pgc01

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

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,225
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top