Date-related conditional formatting -is it possible?

[Gryzor]

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

 

[Gryzor]

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

Cheers

 

[Gryzor]

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 ..

 

[Gryzor]

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

 

[Gryzor]

Hello Max,

a big thank you for your assistance. As I said in my email, I used the
correct column myself indeed and I did try various dates to check the
conditioning... For some reason nothing worked, even when I copied/pasted the
formulas!

Anyhow, I got it working with your help now, but there's one more slight
problem; the first formula [=$F1=TODAY()-7] obviously does not yield results
when I'm within a week from the given date, but only when the cell value is
exactly TODAY()-7. How would I go about formulating a condition for a range?
I've tried a few solutions but they interfere with the second condition...

Thanks again!
Th

 

[Max]

Try this ..

In the sheet: Meow
-------------------------
Let's clear all previous CF on the sheet

Press F5 > Special > Conditional formats > OK

Click Format > Cond Formatting
Click Delete > Check Conds 1 and 2 > OK

Now to set up the CF afresh
---------------------------------
Press CTRL +A
(selects entire sheet, this selection step is important)

Put the CF formulas as:

Cond1: =$F1=""
(Leave it as "No Format Set")

Cond2: =AND($F1<TODAY(),$F1>=TODAY()-7)
(Format > Orange fill color)

Cond3: =$F1>=TODAY()
(Format: Red fill color, white font, bolded)

Click OK
Click OK at the main dialog

Then just delete the CF in row1 (since these are col headers)
To delete, select row1 and
Click Format > Cond Formatting
Click Delete > Check Conds 1 and 2 > OK
Click OK

You should now get entire rows colored orange (Cond2)
for dates in col F which are between 1 - 7 days ago
(instead of just 1 row, exactly 7 days ago)

I'll send to you the revised file with the above implemented..

 

[Max]

Typo correction:

Line

Click Delete > Check Conds 1 and 2 > OK
within
Then just delete the CF in row1 (since these are col headers)
To delete, select row1 and
Click Format > Cond Formatting
Click Delete > Check Conds 1 and 2 > OK
Click OK

should read as:

 

[Gryzor]

Hello again -your help is so greatly appreciated; I'm not an expert in Excel
(and it shows ) but when I help newbies in other IT issues I know what it
is to provide detailed, step-by step instructions...

Anyway, again the funny thing appears -if I insert the formulas in my own
sheet it doesn't seem to work, so I am decidedly nuts, I should guess...

Anyhow, your sheet seems to work indeed, except for one detail -the
not-equals are the wrong way. 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()

Is there something wrong with it? Because, again, it doesn't seem to
work... If I only keep cond.3 it looks good, however Cond2 screws things up.
Any ideas?

Cheers
Th

Try this ..

In the sheet: Meow
-------------------------
Let's clear all previous CF on the sheet

Press F5 > Special > Conditional formats > OK

Click Format > Cond Formatting
Click Delete > Check Conds 1 and 2 > OK

Now to set up the CF afresh
---------------------------------
Press CTRL +A
(selects entire sheet, this selection step is important)

Put the CF formulas as:

Cond1: =$F1=""
(Leave it as "No Format Set")

Cond2: =AND($F1<TODAY(),$F1>=TODAY()-7)
(Format > Orange fill color)

Cond3: =$F1>=TODAY()
(Format: Red fill color, white font, bolded)

Click OK
Click OK at the main dialog

Then just delete the CF in row1 (since these are col headers)
To delete, select row1 and
Click Format > Cond Formatting
Click Delete > Check Conds 1 and 2 > OK
Click OK

You should now get entire rows colored orange (Cond2)
for dates in col F which are between 1 - 7 days ago
(instead of just 1 row, exactly 7 days ago)

I'll send to you the revised file with the above implemented..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hello Max,

a big thank you for your assistance. As I said in my email, I used the
correct column myself indeed and I did try various dates to check the
conditioning... For some reason nothing worked, even when I copied/pasted the
formulas!

Anyhow, I got it working with your help now, but there's one more slight
problem; the first formula [=$F1=TODAY()-7] obviously does not yield results
when I'm within a week from the given date, but only when the cell value is
exactly TODAY()-7. How would I go about formulating a condition for a range?
I've tried a few solutions but they interfere with the second condition...

Thanks again!
Th

 

[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()

Ok .. based on the above reqts ..
this should hopefully give you what you're after ..

Re-do the CF formulas (and formatting):
(Cond1 unchanged)

Cond2: =$F1<=TODAY()-7
(Format: Red fill color, white font, bolded)

Cond3: =AND($F1<TODAY(),$F1>=TODAY()-7)
(Format > Orange fill color)

--
Btw, for Cond3,
think your Excel version uses a ";" instead of a ","

so: =AND($F1<TODAY(),$F1>=TODAY()-7)

should be implemented in your version as:

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

(just an observation from your post ..)

I've sent the file with the above implemented over for your ref.

 

[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)

 

[Gryzor]

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