Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration dates also. Have formatted my dates as 1/1/2005 - it just ignores all the formatting I do. What could possibly be wrong. Appreciate any feedback at all. Niki "Max" wrote: Perhaps try this .. Assuming your due dates are listed in col B, header in row1, dates in B2 down Due date 31-Aug-04 12-Sep-04 01-Dec-04 etc Select col B Click Format Conditional Formatting Make these settings: Condition 1 Formula Is | =B2="" Leave it as "No format set" Click Add (this adds condition 2) Condition 2 Formula Is | =MONTH(TODAY())=MONTH(B2) Click Format button Patterns tab Yellow? OK Click Add (this adds condition 3) Condition 3 Formula Is | =TODAY()B2 Click Format button Patterns tab Pale red? OK Click OK at the main dialog -- Note that you can also simultaneously format the font colour/bold (in the Font tab), etc besides just formatting the fill colour in the Patterns tab -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "canman" wrote in message ... I'm a Newbie that's been missiled into a job that requires many spreadsheets to track recertification dates. The sheets are no problem but I'd like to be able to make due date cells change colour to yellow if in month of recert and red if past recert date. Doable????? thanks for any input -- canman ------------------------------------------------------------------------ canman's Profile: http://www.excelforum.com/member.php...o&userid=14596 View this thread: http://www.excelforum.com/showthread...hreadid=262235 |
#2
![]() |
|||
|
|||
![]()
Hi Niki,
I've tested this so you should be okay. Assume your dates are in column A. Select your dates in col A Click Format Conditional Formatting Make these settings: Condition 1 Formula Is | =(AND(TODAY()=DATE(YEAR(A1),MONTH(A1),1),TODAY()< (DATE(YEAR(A1),MONTH(A1)+1,1)-1))) Click Format button Patterns tab Yellow? OK Click Add (this adds condition 2) Condition 2 Formula Is | =TODAY()=DATE(YEAR(A1),MONTH(A1),1)-1 Click Format button Patterns tab Red? OK Done. Condition 1 checks to see if today's date is greater than the first day of this the month referenced in Col A AND less than the last day of the month referenced in Col A. Condition 2 checks to see if today's date is greater than the last day of the prior month referenced in Col A. I hope this helps. Best regards, Kevin "Niki" wrote in message ... Dear Max: I jumped for joy when I saw this thread. Alas, following the directions exactly - I cannot get it to work. I am tracking expiration dates also. Have formatted my dates as 1/1/2005 - it just ignores all the formatting I do. What could possibly be wrong. Appreciate any feedback at all. Niki "Max" wrote: Perhaps try this .. Assuming your due dates are listed in col B, header in row1, dates in B2 down Due date 31-Aug-04 12-Sep-04 01-Dec-04 etc Select col B Click Format Conditional Formatting Make these settings: Condition 1 Formula Is | =B2="" Leave it as "No format set" Click Add (this adds condition 2) Condition 2 Formula Is | =MONTH(TODAY())=MONTH(B2) Click Format button Patterns tab Yellow? OK Click Add (this adds condition 3) Condition 3 Formula Is | =TODAY()B2 Click Format button Patterns tab Pale red? OK Click OK at the main dialog -- Note that you can also simultaneously format the font colour/bold (in the Font tab), etc besides just formatting the fill colour in the Patterns tab -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "canman" wrote in message ... I'm a Newbie that's been missiled into a job that requires many spreadsheets to track recertification dates. The sheets are no problem but I'd like to be able to make due date cells change colour to yellow if in month of recert and red if past recert date. Doable????? thanks for any input -- canman ------------------------------------------------------------------------ canman's Profile: http://www.excelforum.com/member.php...o&userid=14596 View this thread: http://www.excelforum.com/showthread...hreadid=262235 |
#3
![]() |
|||
|
|||
![]()
Hi, if you'd like to, send me a sample book at :
demechanik <at yahoo <dot com or, xdemechanik <atyahoo<dotcom I'll see what can be done .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Niki" wrote in message ... Dear Max: I jumped for joy when I saw this thread. Alas, following the directions exactly - I cannot get it to work. I am tracking expiration dates also. Have formatted my dates as 1/1/2005 - it just ignores all the formatting I do. What could possibly be wrong. Appreciate any feedback at all. Niki "Max" wrote: Perhaps try this .. Assuming your due dates are listed in col B, header in row1, dates in B2 down Due date 31-Aug-04 12-Sep-04 01-Dec-04 etc Select col B Click Format Conditional Formatting Make these settings: Condition 1 Formula Is | =B2="" Leave it as "No format set" Click Add (this adds condition 2) Condition 2 Formula Is | =MONTH(TODAY())=MONTH(B2) Click Format button Patterns tab Yellow? OK Click Add (this adds condition 3) Condition 3 Formula Is | =TODAY()B2 Click Format button Patterns tab Pale red? OK Click OK at the main dialog -- Note that you can also simultaneously format the font colour/bold (in the Font tab), etc besides just formatting the fill colour in the Patterns tab -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "canman" wrote in message ... I'm a Newbie that's been missiled into a job that requires many spreadsheets to track recertification dates. The sheets are no problem but I'd like to be able to make due date cells change colour to yellow if in month of recert and red if past recert date. Doable????? thanks for any input -- canman ------------------------------------------------------------------------ canman's Profile: http://www.excelforum.com/member.php...o&userid=14596 View this thread: http://www.excelforum.com/showthread...hreadid=262235 |
#4
![]() |
|||
|
|||
![]()
If it completely ignored all the formattings, then what was shown?
I track due dates too, to resolution in term of days before and after a due to track. Presented here, is an improved version to what was previously discussed. It eliminated the condition explicitly written to check whether cell is empty. Assume due dates in column A. Condition 1 Formula Is | =AND(B2<"",B2=TODAY()) = Today is the due date Condition 2 Formula Is | =AND(B2<"",B2-TODAY()<=7) = Date due in one week's time Condition 3 Formula Is | =AND(B2<"",TODAY()B2) = Overdue If the above 3 conditions are not satisfied (i.e. date is due in more than a week's time or cell is empty), the default format will be used. The advantages of this method over the previous one a 1. It allows one more condition. 2. Blank cells are ignored. The disadvantage of this method over the previous one is that it cannot differentiate blank cells from cells with due dates longer than a week. Regards. "Niki" wrote: Dear Max: I jumped for joy when I saw this thread. Alas, following the directions exactly - I cannot get it to work. I am tracking expiration dates also. Have formatted my dates as 1/1/2005 - it just ignores all the formatting I do. What could possibly be wrong. Appreciate any feedback at all. Niki "Max" wrote: Perhaps try this .. Assuming your due dates are listed in col B, header in row1, dates in B2 down Due date 31-Aug-04 12-Sep-04 01-Dec-04 etc Select col B Click Format Conditional Formatting Make these settings: Condition 1 Formula Is | =B2="" Leave it as "No format set" Click Add (this adds condition 2) Condition 2 Formula Is | =MONTH(TODAY())=MONTH(B2) Click Format button Patterns tab Yellow? OK Click Add (this adds condition 3) Condition 3 Formula Is | =TODAY()B2 Click Format button Patterns tab Pale red? OK Click OK at the main dialog -- Note that you can also simultaneously format the font colour/bold (in the Font tab), etc besides just formatting the fill colour in the Patterns tab -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "canman" wrote in message ... I'm a Newbie that's been missiled into a job that requires many spreadsheets to track recertification dates. The sheets are no problem but I'd like to be able to make due date cells change colour to yellow if in month of recert and red if past recert date. Doable????? thanks for any input -- canman ------------------------------------------------------------------------ canman's Profile: http://www.excelforum.com/member.php...o&userid=14596 View this thread: http://www.excelforum.com/showthread...hreadid=262235 |
#5
![]() |
|||
|
|||
![]()
Sorry, the dates are in column B instead of column A.
"ewize1" wrote: If it completely ignored all the formattings, then what was shown? I track due dates too, to resolution in term of days before and after a due to track. Presented here, is an improved version to what was previously discussed. It eliminated the condition explicitly written to check whether cell is empty. Assume due dates in column A. Condition 1 Formula Is | =AND(B2<"",B2=TODAY()) = Today is the due date Condition 2 Formula Is | =AND(B2<"",B2-TODAY()<=7) = Date due in one week's time Condition 3 Formula Is | =AND(B2<"",TODAY()B2) = Overdue If the above 3 conditions are not satisfied (i.e. date is due in more than a week's time or cell is empty), the default format will be used. The advantages of this method over the previous one a 1. It allows one more condition. 2. Blank cells are ignored. The disadvantage of this method over the previous one is that it cannot differentiate blank cells from cells with due dates longer than a week. Regards. "Niki" wrote: Dear Max: I jumped for joy when I saw this thread. Alas, following the directions exactly - I cannot get it to work. I am tracking expiration dates also. Have formatted my dates as 1/1/2005 - it just ignores all the formatting I do. What could possibly be wrong. Appreciate any feedback at all. Niki "Max" wrote: Perhaps try this .. Assuming your due dates are listed in col B, header in row1, dates in B2 down Due date 31-Aug-04 12-Sep-04 01-Dec-04 etc Select col B Click Format Conditional Formatting Make these settings: Condition 1 Formula Is | =B2="" Leave it as "No format set" Click Add (this adds condition 2) Condition 2 Formula Is | =MONTH(TODAY())=MONTH(B2) Click Format button Patterns tab Yellow? OK Click Add (this adds condition 3) Condition 3 Formula Is | =TODAY()B2 Click Format button Patterns tab Pale red? OK Click OK at the main dialog -- Note that you can also simultaneously format the font colour/bold (in the Font tab), etc besides just formatting the fill colour in the Patterns tab -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "canman" wrote in message ... I'm a Newbie that's been missiled into a job that requires many spreadsheets to track recertification dates. The sheets are no problem but I'd like to be able to make due date cells change colour to yellow if in month of recert and red if past recert date. Doable????? thanks for any input -- canman ------------------------------------------------------------------------ canman's Profile: http://www.excelforum.com/member.php...o&userid=14596 View this thread: http://www.excelforum.com/showthread...hreadid=262235 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Ploting dates against a calendar and not as a simple events | Charts and Charting in Excel | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Entering dates in Excel 2002 | Excel Discussion (Misc queries) | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) | |||
Filtering with dates | Excel Discussion (Misc queries) |