B2 is text, A2 is a number formatted as a date. [... and I thought you
said
you were using A1, not A2?]
If you want to compare, use numbers not text. You can format the display
of
a number in a cell to include text, if you wish to do so.
--
David Biddulph
Cells Entry:
Cell A1 - manually entered goods "aging" period begin= 3 mths
Cell A2 - date of goods received = for example 18.06.2008 (dd.mm.yyyy)
Cell B1 - cut-off date = 30.04.2009 (dd.mm.yyyy)
Cell B2 - age of good: formula =DATEDIF(J61,$J$4,"ym")&" mths"
Conditional Formatting on CELL B2:
Cell is / "greater than or equal to" =$A$2
If condition is true = cell backbground color change to blue
Example of Problem encountered cell results:
CELL A1 = 3 mths
CELL A2 = 18.06.2008
CELL B1 = 30.04.2009
CELL B2 = 10 mths
:
Again, we need to see your CF formula to see where you've gone wrong.
--
David Biddulph
I tried to work on months only and again it missed out th 10th & 11th
month.
:
Yes A1
Well thats a good idea as well but generally, we are use to
referring
it
in
mmm-yyy rather then mmm only. I can try but I can foresee
complaints
from
the end user
thanks
:
I assume that your formula actually refers to A1, not A2?
Why not just work in months, rather than years and months?
--
David Biddulph
Cells Entry:
Cell A1 - manually entered goods "aging" period begin= 0 yrs, 3
mths
Cell A2 - date of goods received = for example 18.06.2008
(dd.mm.yyy)
Cell B1 - cut-off date = 30.04.2009 (dd.mm.yyyy)
Cell B2 - age of good: formula =DATEDIF(A2,$B$1,"y")&" yrs,
"&DATEDIF(A2,$B$1,"ym")&" mths"
Conditional Formatting on CELL B2:
Cell is / "greater than or equal to" =$A$2
If condition is true = cell backbground color change to blue
Example of Problem encountered cell results:
CELL A1 = 0 yrs, 3 mths
CELL A2 = 18.06.2008
CELL B1 = 30.04.2009
CELL B2 = 0 yrs, 10 mths
Logic ---> CELL B2 is greater than CELL A1 thus Condition is
TRUE
which
the
background color should be changed to blue, however this does
not
happened
thank you very much.
:
Pl. share the formula you are using..
Most likely you are not taking care of the year part...
If you current month is 1,2 or 3 and date past is in 10, 11,
12
then
current
- past will not be greater than 3.... but for other months it
will
be as
you
expect.
:
Hi, i have applied a conditional formatting for the cell to
highlight
if the
date past 3 months, however it does not work if the months
is
either on
the
10th or 11th month but it works for the rest; 1st to 9th
month
and
1yr
&
above.
Please share your thought in resolving this problem.
Thank you