Date help please

[Terry]

Office 2003
Win XP Pro.

I have a spreadsheet with A)col. "names" and B)col. "dates paid"
My desire is to use another column (say C), with function to highlight "any
date after a certain date has passed".
I have tried conditional formatting but cannot get it right!!

Col A Col.B Col. C
smith 15/2/08 if after 18/2/08 then "bad", otherwise
col. B

TIA
Terry

 

[Pete_UK]

Might be better to use a cell for your reference date, eg D1, rather
than hard-code it within the formula. So, put 18/2/08 in D1 and this
formula in C1:

=IF(B1>D$1,"bad",B1)

Format the cell as a date, and then copy this down to cover the data
you have in column B.

Hope this helps.

Pete

 

[Terry]

I carried on searching for solution and came up with this one, which I can
modify:
=IF(K3 > $R$3, "LATE",IF(K3 < $R$3,""))
Cells are adjusted to suit my new spreadsheet.

I do apologise if any inconvenience guys.

Terry

 

[Pete_UK]

Just check what happens when K3 is the same date as R3.

Pete

 

[Terry]

Peter ...thank you
Terry
Might be better to use a cell for your reference date, eg D1, rather
than hard-code it within the formula. So, put 18/2/08 in D1 and this
formula in C1:

=IF(B1>D$1,"bad",B1)

Format the cell as a date, and then copy this down to cover the data
you have in column B.

Hope this helps.

Pete

 

[Terry]

OK Peter

Amended formula to include = along with <, >, to rid the "false" kicking in.

Terry
Just check what happens when K3 is the same date as R3.

Pete

 

[Pete_UK]

You're welcome, Terry - glad to help.

Pete

Peter ...thank you

Might be better to use a cell for your reference date, eg D1, rather
than hard-code it within the formula. So, put 18/2/08 in D1 and this
formula in C1:

=IF(B1>D$1,"bad",B1)

Format the cell as a date, and then copy this down to cover the data
you have in column B.

Hope this helps.

Pete






- Show quoted text -

 

[Pete_UK]

In which case you can amend it further to:

=IF(K3 > $R$3, "LATE","")

to get the same effect more efficiently.

Hope this helps.

Pete

 

[David Biddulph]

If by that last reply you mean that you've changed your formula from
=IF(K3 > $R$3, "LATE",IF(K3 < $R$3,"")) to
=IF(K3 > $R$3, "LATE",IF(K3 <= $R$3,""))
then you might want to have another think about how you would get to the
second test in any situation where it would not be satisfied, remembering
that you've already done the first test.
In other words, why not just =IF(K3 > $R$3, "LATE", "") ?

If I take your reply literally and you've actually changed the formula to
=IF(K3 >= $R$3, "LATE",IF(K3 <= $R$3,""))
then tghere's even more confusion, in that if you've accepted the = case in
the first test, you can't get to the second test with =.
If = counts as late, then all you need is =IF(K3 >= $R$3, "LATE", "")

 

[Terry]

Appreciated Peter

Terry

In which case you can amend it further to:

=IF(K3 > $R$3, "LATE","")

to get the same effect more efficiently.

Hope this helps.

Pete

 

[Terry]

You are correct David....Peter noticed too.
Thank you all.

Terry