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

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" wrote:

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

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

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

Thank you again.

"Sheeloo" wrote:

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" wrote:

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" wrote:

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" wrote:

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

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" wrote:

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

Thank you again.

"Sheeloo" wrote:

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" wrote:

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" wrote:

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" wrote:

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

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

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


"Amy" wrote:

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

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

"Amy" wrote in message
...
I tried to work on months only and again it missed out th 10th & 11th
month.


"Amy" wrote:

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

Did you try my suggestion?

"Amy" wrote:

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" wrote:

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

Thank you again.

"Sheeloo" wrote:

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" wrote:

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" wrote:

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" wrote:

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

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" wrote:

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

"Amy" wrote in message
...
I tried to work on months only and again it missed out th 10th & 11th
month.


"Amy" wrote:

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

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

"Amy" wrote in message
...
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" wrote:

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

"Amy" wrote in message
...
I tried to work on months only and again it missed out th 10th & 11th
month.


"Amy" wrote:

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

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.

"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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

"Amy" wrote in message
...
I tried to work on months only and again it missed out th 10th & 11th
month.


"Amy" wrote:

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

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

"Amy" wrote in message
...
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.

"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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

"Amy" wrote in message
...
I tried to work on months only and again it missed out th 10th & 11th
month.


"Amy" wrote:

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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

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.

"David Biddulph" wrote:

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

"Amy" wrote in message
...
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.

"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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

"Amy" wrote in message
...
I tried to work on months only and again it missed out th 10th & 11th
month.


"Amy" wrote:

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


"David Biddulph" wrote:

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

"Amy" wrote in message
...
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" wrote:

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" wrote:

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