|
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
Conditional formatting
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 |
| All times are GMT +1. The time now is 03:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com