Conditional formatting

[Amy]

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

 

[Sheeloo]

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.

 

[Amy]

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.

 

[Sheeloo]

You are comparing text values...
0 yrs, 10 mths is less than 0 yrs, 3 mths when you sort as TEXT
You can put these values and sort to see

Enter 3 in A1
6/18/2008 in A2
4/30/2009 in B1
=DATEDIF(A2,B1,"m") in B2

 

[Amy]

thanks, i managed to highlight the 10th & 11th month by entering 3 in
cell A1 and retain the rest as original.

Thank you again.

 

[Amy]

Oops, speak too soon. My steps did not work. It highlights all the months
including those less than or equal to 3 months.

PLEASE help.

Thanks

 

[David Biddulph]

I assume that your formula actually refers to A1, not A2?
Why not just work in months, rather than years and months?

 

[Amy]

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

 

[Amy]

I tried to work on months only and again it missed out th 10th & 11th month.

 

[David Biddulph]

Again, we need to see your CF formula to see where you've gone wrong.

 

[Sheeloo]

Did you try my suggestion?

Oops, speak too soon. My steps did not work. It highlights all the months
including those less than or equal to 3 months.

PLEASE help.

Thanks

 

[Amy]

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

 

[David Biddulph]

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.

 

[Amy]

it works when we convert to number but it did not take the age past 13 months
but only takes the month,i.e. 1 for 1 yr 1 mth result.

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

 

[David Biddulph]

You don't want "ym" in the DATEDIF formula; you want "m".
--
David Biddulph

it works when we convert to number but it did not take the age past 13
months
but only takes the month,i.e. 1 for 1 yr 1 mth result.

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

 

[Amy]

Thank you, it works, Finally!! yahoo. highlighted the 10th, 11th 13th & 13th
month.

sheeloo - I did try your suggestion and thank you for your help.

You don't want "ym" in the DATEDIF formula; you want "m".
--
David Biddulph

it works when we convert to number but it did not take the age past 13
months
but only takes the month,i.e. 1 for 1 yr 1 mth result.

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