Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell text color change
Here's my situation
I have 3 colums 1. Last completed 2. 6 month expiration 3. 12 month expiration Both the 6 month and 12 month colums have grace periods of date +1 month The +1 month would be end of month, so if the expiration date is 08/20/2007 the grace period would be valid until 09/30/2007 I would like excel to change the text color to Blue if the current date is beyond the expiration date but within the grace period. I would like excel to change the text color to Red if the current date is beyond the grace period I currently have it set to change the color using conditional formating if it is beyond the current date =AND(D3<"",TODAY()-D30) But i do not know how to set it up to change the color if the current date is within the grace period and change it to a different color if the current date is beyond the grace period. any help would be appreciated. Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell text color change
Jeff,
I was recently working on something like this. I don't always get these right the first time, but I think this will work for you: =IF(DATE(YEAR(D3),MOD(MONTH(D3)+2,12),1)-1 TODAY(),DATE(YEAR(D3),MOD(MONTH(D3)+2,12),1)-1,DATE(YEAR(D3)+1,MOD(MONTH(D3)+2,12),1)-1) It basically calculates the first day of the month after next and then subtracts 1, giving you the last day of next month. The If and the Mod deal with if your date is in December. Chip Pearson has a bunch of this stuff - and may have a better answer - at: http://www.cpearson.com/excel/DateTimeWS.htm hth, Doug wrote in message oups.com... Here's my situation I have 3 colums 1. Last completed 2. 6 month expiration 3. 12 month expiration Both the 6 month and 12 month colums have grace periods of date +1 month The +1 month would be end of month, so if the expiration date is 08/20/2007 the grace period would be valid until 09/30/2007 I would like excel to change the text color to Blue if the current date is beyond the expiration date but within the grace period. I would like excel to change the text color to Red if the current date is beyond the grace period I currently have it set to change the color using conditional formating if it is beyond the current date =AND(D3<"",TODAY()-D30) But i do not know how to set it up to change the color if the current date is within the grace period and change it to a different color if the current date is beyond the grace period. any help would be appreciated. Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Partial Text Color Change in Cell | Excel Discussion (Misc queries) | |||
Copy text cell & change text color conditionally | Excel Programming | |||
Cell text color change | Excel Discussion (Misc queries) | |||
Change Cell-Background color into Text | Excel Programming | |||
Change Cell-Background color into Text | Excel Programming |