Conditional Formatting
I have a formula in Conditional formatting that changes the color of the cell
if a date in that cell is equal to today's date less 2 months and todays date plus 2 months. This works fine thanks to you guys helping me with the formula. However, if I change the date in the cell because there is now a new retest date and the date is more than 2 months, the cell stays red. How can I get the cell to change to a different color if I put in a new date that does not meet the criteria. Thanks again for any help. Best regards, Dee |
Conditional Formatting
It should work okay. What is the formula, and do you have manual calculation
set? -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... I have a formula in Conditional formatting that changes the color of the cell if a date in that cell is equal to today's date less 2 months and todays date plus 2 months. This works fine thanks to you guys helping me with the formula. However, if I change the date in the cell because there is now a new retest date and the date is more than 2 months, the cell stays red. How can I get the cell to change to a different color if I put in a new date that does not meet the criteria. Thanks again for any help. Best regards, Dee |
Conditional Formatting
Hi Bob,
The formula is: =AND(F3=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY ())+2,DAY(TODAY())))=AND(F3=DATE(YEAR(TODAY()),MO NTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY ())+2,DAY(TODAY()))) If I change the cell to a different retest date that is say 6 months from today, the cell remains red in color. Should it not change to the default black? Thanks very much for your help. Regards, Dee "Bob Phillips" wrote: It should work okay. What is the formula, and do you have manual calculation set? -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... I have a formula in Conditional formatting that changes the color of the cell if a date in that cell is equal to today's date less 2 months and todays date plus 2 months. This works fine thanks to you guys helping me with the formula. However, if I change the date in the cell because there is now a new retest date and the date is more than 2 months, the cell stays red. How can I get the cell to change to a different color if I put in a new date that does not meet the criteria. Thanks again for any help. Best regards, Dee |
Conditional Formatting
Dee,
As far as I can see, you are comparing two identical formula, so it will always resolve to true, so always be red. Just strip it back to one test =AND(F3=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD AY()),MONTH(TODAY())+2,DAY(TODAY()))) -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... Hi Bob, The formula is: =AND(F3=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD AY()),MONTH(TODAY())+2,DAY(TODAY())))=AND(F3=DATE (YEAR(TODAY()),MONTH(TODAY ())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY ())+2,DAY(TODAY()))) If I change the cell to a different retest date that is say 6 months from today, the cell remains red in color. Should it not change to the default black? Thanks very much for your help. Regards, Dee "Bob Phillips" wrote: It should work okay. What is the formula, and do you have manual calculation set? -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... I have a formula in Conditional formatting that changes the color of the cell if a date in that cell is equal to today's date less 2 months and todays date plus 2 months. This works fine thanks to you guys helping me with the formula. However, if I change the date in the cell because there is now a new retest date and the date is more than 2 months, the cell stays red. How can I get the cell to change to a different color if I put in a new date that does not meet the criteria. Thanks again for any help. Best regards, Dee |
Conditional Formatting
Dear Bob,
I deleted one instance of the formual and everything is working perfect. Thank you so much for your help. You are awesome! Best regards, Dee "Bob Phillips" wrote: Dee, As far as I can see, you are comparing two identical formula, so it will always resolve to true, so always be red. Just strip it back to one test =AND(F3=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD AY()),MONTH(TODAY())+2,DAY(TODAY()))) -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... Hi Bob, The formula is: =AND(F3=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD AY()),MONTH(TODAY())+2,DAY(TODAY())))=AND(F3=DATE (YEAR(TODAY()),MONTH(TODAY ())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY ())+2,DAY(TODAY()))) If I change the cell to a different retest date that is say 6 months from today, the cell remains red in color. Should it not change to the default black? Thanks very much for your help. Regards, Dee "Bob Phillips" wrote: It should work okay. What is the formula, and do you have manual calculation set? -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... I have a formula in Conditional formatting that changes the color of the cell if a date in that cell is equal to today's date less 2 months and todays date plus 2 months. This works fine thanks to you guys helping me with the formula. However, if I change the date in the cell because there is now a new retest date and the date is more than 2 months, the cell stays red. How can I get the cell to change to a different color if I put in a new date that does not meet the criteria. Thanks again for any help. Best regards, Dee |
Conditional Formatting
Always helps when we can see the formula :-))
Glad it worked. Regards Bob "Dee" wrote in message ... Dear Bob, I deleted one instance of the formual and everything is working perfect. Thank you so much for your help. You are awesome! Best regards, Dee "Bob Phillips" wrote: Dee, As far as I can see, you are comparing two identical formula, so it will always resolve to true, so always be red. Just strip it back to one test =AND(F3=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD AY()),MONTH(TODAY())+2,DAY(TODAY()))) -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... Hi Bob, The formula is: =AND(F3=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),F3<=DATE(YEAR(TOD AY()),MONTH(TODAY())+2,DAY(TODAY())))=AND(F3=DATE (YEAR(TODAY()),MONTH(TODAY ())-2,DAY(TODAY())),F3<=DATE(YEAR(TODAY()),MONTH(TODAY ())+2,DAY(TODAY()))) If I change the cell to a different retest date that is say 6 months from today, the cell remains red in color. Should it not change to the default black? Thanks very much for your help. Regards, Dee "Bob Phillips" wrote: It should work okay. What is the formula, and do you have manual calculation set? -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... I have a formula in Conditional formatting that changes the color of the cell if a date in that cell is equal to today's date less 2 months and todays date plus 2 months. This works fine thanks to you guys helping me with the formula. However, if I change the date in the cell because there is now a new retest date and the date is more than 2 months, the cell stays red. How can I get the cell to change to a different color if I put in a new date that does not meet the criteria. Thanks again for any help. Best regards, Dee |
All times are GMT +1. The time now is 04:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com