|
Date Function
I want to put conditional formatting into a cell where I want excel to change
the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
Hi, Dee. Try this:
http://www.officearticles.com/excel/...ft_ excel.htm ************ Anne Troy www.OfficeArticles.com "Dee" wrote in message ... I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
Hi Dee,
Try the following: - Select the cell(s) you wish to conditional format and select conditional formatting from the menu. - Change condition to 'Formula Is' (rather than 'Cell Value Is') - Paste this formula into the text box: =AND(B1<=TODAY(),IF(MONTH(TODAY())2,B1=DATE(YEAR (TODAY()),MONTH(TODAY())-2,DAY(TODAY())),B1=DATE(YEAR(TODAY())-1,MONTH(TODAY())+10,DAY(TODAY())))) - Replace the occurrences of B1 with the cell that is currently active (you should be able to see this in the worksheet / formula bar behind). - Select your formatting options and click OK. Let me know how you get on! Cheers, Mark "Dee" wrote: I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
Use a formula of
=AND(A1=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY()))<A1<=TODAY()) where A1 is the first cell to be CFed. -- HTH RP (remove nothere from the email address if mailing direct) "Dee" wrote in message ... I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
=AND(A1<=TODAY(),A1=DATE(YEAR(TODAY()),MONTH(TODA Y())-2,DAY(TODAY())))
should work. However, you should be able to use the EDATE function instead of the DATE function to determine 2 months prior. I can't seem to get it to work though. When I try it I get a "You may not use references to other worksheets or workbooks for conditional formatting criteria." I don't see the reference to the other sheet or book though. But the first formula I gave works. Thanks, Bill Horton "Dee" wrote: I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
Hi Mark,
It worked! You guys are awesome! If I wanted to extend the conditional formatting to include not only 2 months prior but also 2 months in advance, how would I do that. What I'm trying to do is be alerted to retest dates and some of the dates have already past and I would like to be alerted if a retest date is coming up within 2 months. Thanks again for all your help. I learn so much from you guys. Best regards, Dee "Mark Hone" wrote: Hi Dee, Try the following: - Select the cell(s) you wish to conditional format and select conditional formatting from the menu. - Change condition to 'Formula Is' (rather than 'Cell Value Is') - Paste this formula into the text box: =AND(B1<=TODAY(),IF(MONTH(TODAY())2,B1=DATE(YEAR (TODAY()),MONTH(TODAY())-2,DAY(TODAY())),B1=DATE(YEAR(TODAY())-1,MONTH(TODAY())+10,DAY(TODAY())))) - Replace the occurrences of B1 with the cell that is currently active (you should be able to see this in the worksheet / formula bar behind). - Select your formatting options and click OK. Let me know how you get on! Cheers, Mark "Dee" wrote: I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
=AND(A1=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),A1<=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 Mark, It worked! You guys are awesome! If I wanted to extend the conditional formatting to include not only 2 months prior but also 2 months in advance, how would I do that. What I'm trying to do is be alerted to retest dates and some of the dates have already past and I would like to be alerted if a retest date is coming up within 2 months. Thanks again for all your help. I learn so much from you guys. Best regards, Dee "Mark Hone" wrote: Hi Dee, Try the following: - Select the cell(s) you wish to conditional format and select conditional formatting from the menu. - Change condition to 'Formula Is' (rather than 'Cell Value Is') - Paste this formula into the text box: =AND(B1<=TODAY(),IF(MONTH(TODAY())2,B1=DATE(YEAR (TODAY()),MONTH(TODAY())-2 ,DAY(TODAY())),B1=DATE(YEAR(TODAY())-1,MONTH(TODAY())+10,DAY(TODAY())))) - Replace the occurrences of B1 with the cell that is currently active (you should be able to see this in the worksheet / formula bar behind). - Select your formatting options and click OK. Let me know how you get on! Cheers, Mark "Dee" wrote: I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
It worked! Thank you so much!
"Bob Phillips" wrote: =AND(A1=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),A1<=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 Mark, It worked! You guys are awesome! If I wanted to extend the conditional formatting to include not only 2 months prior but also 2 months in advance, how would I do that. What I'm trying to do is be alerted to retest dates and some of the dates have already past and I would like to be alerted if a retest date is coming up within 2 months. Thanks again for all your help. I learn so much from you guys. Best regards, Dee "Mark Hone" wrote: Hi Dee, Try the following: - Select the cell(s) you wish to conditional format and select conditional formatting from the menu. - Change condition to 'Formula Is' (rather than 'Cell Value Is') - Paste this formula into the text box: =AND(B1<=TODAY(),IF(MONTH(TODAY())2,B1=DATE(YEAR (TODAY()),MONTH(TODAY())-2 ,DAY(TODAY())),B1=DATE(YEAR(TODAY())-1,MONTH(TODAY())+10,DAY(TODAY())))) - Replace the occurrences of B1 with the cell that is currently active (you should be able to see this in the worksheet / formula bar behind). - Select your formatting options and click OK. Let me know how you get on! Cheers, Mark "Dee" wrote: I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
Better than my first miserable attempt :-)
Bob "Dee" wrote in message ... It worked! Thank you so much! "Bob Phillips" wrote: =AND(A1=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),A1<=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 Mark, It worked! You guys are awesome! If I wanted to extend the conditional formatting to include not only 2 months prior but also 2 months in advance, how would I do that. What I'm trying to do is be alerted to retest dates and some of the dates have already past and I would like to be alerted if a retest date is coming up within 2 months. Thanks again for all your help. I learn so much from you guys. Best regards, Dee "Mark Hone" wrote: Hi Dee, Try the following: - Select the cell(s) you wish to conditional format and select conditional formatting from the menu. - Change condition to 'Formula Is' (rather than 'Cell Value Is') - Paste this formula into the text box: =AND(B1<=TODAY(),IF(MONTH(TODAY())2,B1=DATE(YEAR (TODAY()),MONTH(TODAY())-2 ,DAY(TODAY())),B1=DATE(YEAR(TODAY())-1,MONTH(TODAY())+10,DAY(TODAY())))) - Replace the occurrences of B1 with the cell that is currently active (you should be able to see this in the worksheet / formula bar behind). - Select your formatting options and click OK. Let me know how you get on! Cheers, Mark "Dee" wrote: I want to put conditional formatting into a cell where I want excel to change the color of the date in the cell if it date is anywhere between today's date and 2 months prior to today's date. So if today's date is August 12th and the date in the cell is anywhere between June 12th and August 12th the cell will change color. I am using Excell 2003 Thanks very much for your help. Best regards, Dee |
| All times are GMT +1. The time now is 11:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com