• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date reminders/alerts

coriolis

New Member
Hi,


Im working on a spreadsheet for my employer. Its a record of all his quotes and sales. He has a column where he records the date he last contacted each client and want excel to change the colour of the cell after a set amount of time if he hasn't updated that cell.


For example, if he enters 10/03/10, on the 17/03/10 he would like that cell to change colour to remind him that he needs to take action. He would then enter 17/03/10 and 7 days later would be reminded again.


Any help much appreciated !


Thanks
 

Clarity

New Member
Hi,

Chandoo has a useful post that should point you in the right direction:


http://chandoo.org/wp/2010/01/05/conditional-formatting-dates/


Myles
 

Hui

Excel Ninja
Staff member
Coriolis

Use conditionl Formatting

Use a Formula


If the Date Cell is C2 use

=C2<=Now()

and setup whatever format you want
 

coriolis

New Member
Hey everyone, thanks for the help.


oldchippy, how would i use your formula to format a whole column, from lets say, c4 to infinity?


Ill have a go at making it work myself in the meantime.


Thanks
 

Hui

Excel Ninja
Staff member
Coriolis

Same deal

Select the range

Use conditionl Formatting

Use a Formula


If the Date Cell is C2 use

=C2<=Now() or something else

and setup whatever format you want


Excel will adjust each cells formula accordingly
 

oldchippy

Active Member
Hi,


This link from Chandoo will also help


http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/
 

coriolis

New Member
Thanks again to everyone,


I have it working using the formula =D5<=NOW()-7

and the range is set to =$D$5:$D$1000


How would i make it ignore blank cells? i only want it to format cells with a date entered.


Thanks again
 

TessaES

New Member
replace the ";" with a ","

(I have the dutch version, which uses "," as a decimal separator and thus uses ";" to separate arguments in formulas)
 
Top