Date-related conditional formatting -is it possible?

[Guest]

Hello people...

There's a style of conditional formatting I'm trying to do but I haven't
succeeded in doing anything so far, so I thought maybe you could help me?

I have a list, basically, and one of the columns contains dates (in a yymmdd
format). What I want to do is for a way to check these dates against today's
date and

a. if date=today-7 then the row or date cell should turn, say, orange
b. if date>=today then the row or date cell should turn, say, red

Is there a way to accomplish this?

Thanks in advance!
Th.

 

[Bob Phillips]

Select all the dates (say A1:A100)

In CF, change condition 1 to Formula is, and add a formula of

=A1=TODAY()-7, then format

add a condition, also formula is

=A1>=TODAY(), then format

OK out

 

[Guest]

Darn! What a fast reply... Thanks, I'll try to figure it out before I pester
you more

Cheers

 

[Guest]

Hmmm....? Strange again, it seems like it should work and yet it doesn't? It
appears to ignore the formula and paints all cells orange...

And it does not paint the whole line whose date is being checked, but this
is really minor...

Any advice?

Cheers
Th

 

[Max]

Hmmm....? Strange again, it seems like it should work and yet it doesn't? It
appears to ignore the formula and paints all cells orange...

Bob's C.F. formulas work, but perhaps the col's "yymmdd" dates
are actually text, not dates

Try this to convert ..

Select the column (assume it's col A)

Click Data > Text to columns
Click Next > Next

In step3 of the wiz.:
Check "Date" under Column data format & select "YMD" in the droplist
Click Finish

The CF should now work

And it does not paint the whole line whose date is being checked, but this
is really minor...

Try these slight tweaks to get whole rows formatted, not just cells in col A

Press CTRL +A
(selects entire sheet)

Put the CF formulas as:
Cond1: =$A1=TODAY()-7
Cond2: =$A1>=TODAY()

(above is the same as what Bob suggested except that
"$" is used to "fix" the references in all cols to col A)

The entire row(s) should now be formatted ..

 

[Guest]

Hello Max,

thanks for yout help, I really appreciate it. However it still does not
appear to work... I had already converted my dates to more conventional
formats (currently using dd/mm/yy), and again, is just uses the colour from
the second condition to paint the whole page!

Using O2k3, in case this is relevant. Maybe I'm doing something
fundamentally wrong? But then again there are not many things that I can do
wrong... Would it be easy for you (or anyone, for that matter) to check it if
I upload the sheet somewhere?

Thanks again,
Th

 

[Max]

Maybe email a copy over to:
(e-mail address removed)

I'll take a look ..

 

[Max]

Possibly one of 2 reasons why it didn't work earlier ..:
either implementation (OP had dates in col F, not col A) or,
due to *all* the sample dates in col F satisfying cond2 of the CF, hence
resulting in all the rows having cond2's format
(which is as per CF specs <g>)

Suggestion implemented in the file returned since to OP

 

[Max]

Since I'm looking to be notified when I'm

a.within 7 days from the Due Date (column F) and
b.when the Due date has passed, I guess it should be :

Cond2: =AND($F1>TODAY();$F1<=TODAY()-7)
Cond3: =$F1<=TODAY()

Just a last go at it ..
the tough part is in the interp <g>

Entire rows to be red if due date has passed
Entire rows to be yellow if due date is within a week from now (TODAY()+7)
(Implication: Rows with due dates greater than a week from now
are to remain unformatted .. )

The CF formulas implemented in the sample file sent over were:

Cond1: =$F1=""
(Unformatted)
(Cond1 unchanged)

Cond2: =$F1<=TODAY()
(Red, font bolded/white)

Cond3: =AND($F1>TODAY(),$F1<=TODAY()+7)
(Orange)

 

[Guest]

Well, Max figured it out for me... so a huge thank you for helping me out!

The weird thing is that, although the formulas he suggests work fine in the
sheet he has sent to me, when I try to implement them in my own sheet refuse
to work -sometimes at all, sometimes they do but erratically... So I just
copied my data over to the sheet he sent me, but I was wondering if anyone's
seen this before?

Cheers
Th

....

 

[Gord Dibben]

Gryzor

Perhaps some of the "dates" on your sheet are not dates, but text.

Copying to Max's sheet forced them to real dates??

Try =ISNUMBER(cellref) on the "dates" that don't work.

A real date will return TRUE

Gord Dibben Excel MVP