Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Conditional Formating for dates beyond 30, 60, 90 to be high light

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Conditional Formating for dates beyond 30, 60, 90 to be high light

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Conditional Formating for dates beyond 30, 60, 90 to be high l

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Conditional Formating for dates beyond 30, 60, 90 to be high l

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formating on dates Margotbf Excel Discussion (Misc queries) 6 May 28th 08 02:46 PM
Excel should high-light Rows & Columns when cell selected. RS Setting up and Configuration of Excel 1 June 28th 06 11:26 AM
Install dates formating using conditional formating? Jerry Eggleston Excel Discussion (Misc queries) 2 November 9th 05 05:49 PM
CONDITIONAL FORMATING DATES tomklem Excel Discussion (Misc queries) 3 October 7th 05 04:24 PM
high light row your working in kcholly Excel Discussion (Misc queries) 3 March 29th 05 08:35 AM


All times are GMT +1. The time now is 07:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"