Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
My spread sheet has sales prospects names, numbers, titles, etc. I also have
a column titled "last date of contact" with dates in the cells. I want the dates in these cells to change color or the cell to be high lighted when that date goes beyond 30, 60 or 90 days. Thanks |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi,
Select the range of dates then Format|Conditional format cell value is - less than - =TODAY()-90 format - pick a colour ADD cell value is - less than - =TODAY()-60 format - pick a colour ADD cell value is - less than - =TODAY()-30 format - pick a colour OK Mike "dmorlando" wrote: My spread sheet has sales prospects names, numbers, titles, etc. I also have a column titled "last date of contact" with dates in the cells. I want the dates in these cells to change color or the cell to be high lighted when that date goes beyond 30, 60 or 90 days. Thanks |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Mike, thank you for the info. I need to test my understanding. based on the
conditional formating you gave me what should happen is; once one or more of the dates in this column go beyond 30, 60, 90, days from todays date or what ever the date is each time I open the work book then I should see the colors I picked.....correct? The only date that changed in color is one from 2008 which in fact wold be 90 days old from today and red is the color I chose for 90 days. I'm still a little confused. "Mike H" wrote: Hi, Select the range of dates then Format|Conditional format cell value is - less than - =TODAY()-90 format - pick a colour ADD cell value is - less than - =TODAY()-60 format - pick a colour ADD cell value is - less than - =TODAY()-30 format - pick a colour OK Mike "dmorlando" wrote: My spread sheet has sales prospects names, numbers, titles, etc. I also have a column titled "last date of contact" with dates in the cells. I want the dates in these cells to change color or the cell to be high lighted when that date goes beyond 30, 60 or 90 days. Thanks |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi,
it's important you enter the CF's in the order I gave you because if you enter =today()-30 first then it will never evaluate today()-60 etc. Once condition 1 evaluate as TRUE it stops and doesn't even bother with the next 2 conditions. So if a client was visited on 27/2/2009 and the worksheet calculates then none of the conditions evaluate as true but as today() moves on the visited date becomes more remote from today and and eventually gets to today()-30 = TRUE then conditions 1 & 2 evaluate as FALSE but condition 3 evaluates as TRUE and the cell changes colour. As we move on in time then ultimately today()-60 becomes TRUE etc. It doesn't apply when the workbook is opened it takes effect whenever the workbook calculates Mike "dmorlando" wrote: Mike, thank you for the info. I need to test my understanding. based on the conditional formating you gave me what should happen is; once one or more of the dates in this column go beyond 30, 60, 90, days from todays date or what ever the date is each time I open the work book then I should see the colors I picked.....correct? The only date that changed in color is one from 2008 which in fact wold be 90 days old from today and red is the color I chose for 90 days. I'm still a little confused. "Mike H" wrote: Hi, Select the range of dates then Format|Conditional format cell value is - less than - =TODAY()-90 format - pick a colour ADD cell value is - less than - =TODAY()-60 format - pick a colour ADD cell value is - less than - =TODAY()-30 format - pick a colour OK Mike "dmorlando" wrote: My spread sheet has sales prospects names, numbers, titles, etc. I also have a column titled "last date of contact" with dates in the cells. I want the dates in these cells to change color or the cell to be high lighted when that date goes beyond 30, 60 or 90 days. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
conditional formating on dates | Excel Discussion (Misc queries) | |||
Excel should high-light Rows & Columns when cell selected. | Setting up and Configuration of Excel | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATING DATES | Excel Discussion (Misc queries) | |||
high light row your working in | Excel Discussion (Misc queries) |