|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 11th, 2009, 09:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting from Dates
Today's date will be manually entered in to a header cell.
The worksheet contains the dates that customers last purchased from me. How can I change the colour of the customer dates (and other details) eg. To Red if older than a year, to Green if less than a year? Apologies if this has a simple/obvious solution! Many thks, -- DavidM 
|
|
#2
June 11th, 2009, 09:40 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formatting from Dates
Hi David
Excel will be able to identify the current date. So you dont need to enter the current date to the header cell. Try the below in cell A1 of a fresh workbook and feedback From menu FormatConditional Formatting In Condition1 select 'Formula Is' and enter =DATEDIF(A1,TODAY(),"y")0 and select red color from FormatPattern In Condition2 select 'Formula Is' and enter =DATEDIF(A1,TODAY(),"y")=0 and select green color from FormatPattern If this post helps click Yes --------------- Jacob Skaria "DavidM" wrote: Today's date will be manually entered in to a header cell. The worksheet contains the dates that customers last purchased from me. How can I change the colour of the customer dates (and other details) eg. To Red if older than a year, to Green if less than a year? Apologies if this has a simple/obvious solution! Many thks, -- DavidM
|
|
#3
June 11th, 2009, 03:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formatting from Dates
Thank you Jacob - that works absolutely great!
I wonder if you could explain it to me as I fail to see where "it" understands the 12 months! Thks -- DavidM "Jacob Skaria" wrote: Hi David Excel will be able to identify the current date. So you dont need to enter the current date to the header cell. Try the below in cell A1 of a fresh workbook and feedback From menu FormatConditional Formatting In Condition1 select 'Formula Is' and enter =DATEDIF(A1,TODAY(),"y")0 and select red color from FormatPattern In Condition2 select 'Formula Is' and enter =DATEDIF(A1,TODAY(),"y")=0 and select green color from FormatPattern If this post helps click Yes --------------- Jacob Skaria "DavidM" wrote: Today's date will be manually entered in to a header cell. The worksheet contains the dates that customers last purchased from me. How can I change the colour of the customer dates (and other details) eg. To Red if older than a year, to Green if less than a year? Apologies if this has a simple/obvious solution! Many thks, -- DavidM
|
|
#4
June 11th, 2009, 05:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formatting from Dates
1 year = 12 months.
If you wanted you could use =DATEDIF(A1,TODAY(),"y")=1 or =DATEDIF(A1,TODAY(),"m")=12 or =DATEDIF(A1,TODAY(),"m")11 instead of =DATEDIF(A1,TODAY(),"y")0 -- David Biddulph "DavidM" wrote in message ... Thank you Jacob - that works absolutely great! I wonder if you could explain it to me as I fail to see where "it" understands the 12 months! Thks -- DavidM "Jacob Skaria" wrote: Hi David Excel will be able to identify the current date. So you dont need to enter the current date to the header cell. Try the below in cell A1 of a fresh workbook and feedback From menu FormatConditional Formatting In Condition1 select 'Formula Is' and enter =DATEDIF(A1,TODAY(),"y")0 and select red color from FormatPattern In Condition2 select 'Formula Is' and enter =DATEDIF(A1,TODAY(),"y")=0 and select green color from FormatPattern If this post helps click Yes --------------- Jacob Skaria "DavidM" wrote: Today's date will be manually entered in to a header cell. The worksheet contains the dates that customers last purchased from me. How can I change the colour of the customer dates (and other details) eg. To Red if older than a year, to Green if less than a year? Apologies if this has a simple/obvious solution! Many thks, -- DavidM
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
