Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
I have a spreadsheet that list all my recruiter's clients and when the last
time they had lunch with them. I am looking for a way to change the font color depending on when the last time they had lunch with them. Here is what I am looking for. Year and Over: Red font 6 months to a year: Orange font current date to 6 months: Green Font What would be the formulas to get these to change and keep up with the most current date (being today) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
Use Conditional Formatting, not worksheet functions.
Which column is your date in? Do you want all the cells on a particular row to change colour, or just the date column? Pete On Jan 16, 1:50*pm, Melissa44 wrote: I have a spreadsheet that list all my recruiter's clients and when the last time they had lunch with them. *I am looking for a way to change the font color depending on when the last time they had lunch with them. *Here is what I am looking for. Year and Over: Red font 6 months to a year: Orange font current date to 6 months: Green Font What would be the formulas to get these to change and keep up with the most current date (being today) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
Hi Melissa,.
In cell D1 enter the formula to bring todays date =today() then highlight the column where you have the dates you want to highlight with colours and do conditional formating, where the formula is enter =($D$1-D)180 then select the colour you want "Melissa44" wrote: I have a spreadsheet that list all my recruiter's clients and when the last time they had lunch with them. I am looking for a way to change the font color depending on when the last time they had lunch with them. Here is what I am looking for. Year and Over: Red font 6 months to a year: Orange font current date to 6 months: Green Font What would be the formulas to get these to change and keep up with the most current date (being today) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
First CF condition/ Formula Is/ =DATEDIF(A2,TODAY(),"y")=1 for your Red
Second CF condition/ Formula Is/ =DATEDIF(A3,TODAY(),"m")=6 for Orange Default formatting Green (and perhaps also test for blank cells if this might be an option). -- David Biddulph "Melissa44" wrote in message ... I have a spreadsheet that list all my recruiter's clients and when the last time they had lunch with them. I am looking for a way to change the font color depending on when the last time they had lunch with them. Here is what I am looking for. Year and Over: Red font 6 months to a year: Orange font current date to 6 months: Green Font What would be the formulas to get these to change and keep up with the most current date (being today) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
My date [=Today()] is in K1. The column where I need to change the colors is
E3 to E175. If its less than 180= green If its more than 180 but less than 360= Orange If its more than 360 =red "Pete_UK" wrote: Use Conditional Formatting, not worksheet functions. Which column is your date in? Do you want all the cells on a particular row to change colour, or just the date column? Pete On Jan 16, 1:50 pm, Melissa44 wrote: I have a spreadsheet that list all my recruiter's clients and when the last time they had lunch with them. I am looking for a way to change the font color depending on when the last time they had lunch with them. Here is what I am looking for. Year and Over: Red font 6 months to a year: Orange font current date to 6 months: Green Font What would be the formulas to get these to change and keep up with the most current date (being today) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
Okay I tried that and it didnt work. Or I messed it up somehow.
Here are the example dates I have: 10/17/2008 12/23/2008 11/28/2007 12/10/2008 12/10/2008 So everything in Dec should be green (which I changed my font default to green), 11/28/07 should turn red. So I highlighted column E because that is the dates I want to change colors. I made my default color font green. I click on FormatConditonal Formatting. First my first one, I click the drop down box and changed "Cell value" to "Formula is". Then I copy and pasted the formula you said =DATEDIF(A2,TODAY(),"y")=1 and changed the font color to red. Then I added another Conditions. In Condition 2, I changed the "Cell value" is to "Formula is" and pasted the second formula you gave me =DATEDIF(A3,TODAY(),"m")=6 and changed that font to orange. Then I clicked okay and nothing happened. In (K1) is where I have the date =Today()? Did I not do something right? "David Biddulph" wrote: First CF condition/ Formula Is/ =DATEDIF(A2,TODAY(),"y")=1 for your Red Second CF condition/ Formula Is/ =DATEDIF(A3,TODAY(),"m")=6 for Orange Default formatting Green (and perhaps also test for blank cells if this might be an option). -- David Biddulph "Melissa44" wrote in message ... I have a spreadsheet that list all my recruiter's clients and when the last time they had lunch with them. I am looking for a way to change the font color depending on when the last time they had lunch with them. Here is what I am looking for. Year and Over: Red font 6 months to a year: Orange font current date to 6 months: Green Font What would be the formulas to get these to change and keep up with the most current date (being today) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for dates
You need to put the correct cell references in the Conditional Formatting formulas.
When you say "I highlighted column E", did you actually click on the "E" column header, or did you select E3:E175 (your desired range per another post in this thread)? Assuming the second, and that E3 is the currently active cell within that selection, your CF formulas would be as follows: =DATEDIF(E3,TODAY(),"y")=1 =DATEDIF(E3,TODAY(),"m")=6 K1 is not necessary. Melissa44 wrote: Okay I tried that and it didnt work. Or I messed it up somehow. Here are the example dates I have: 10/17/2008 12/23/2008 11/28/2007 12/10/2008 12/10/2008 So everything in Dec should be green (which I changed my font default to green), 11/28/07 should turn red. So I highlighted column E because that is the dates I want to change colors. I made my default color font green. I click on FormatConditonal Formatting. First my first one, I click the drop down box and changed "Cell value" to "Formula is". Then I copy and pasted the formula you said =DATEDIF(A2,TODAY(),"y")=1 and changed the font color to red. Then I added another Conditions. In Condition 2, I changed the "Cell value" is to "Formula is" and pasted the second formula you gave me =DATEDIF(A3,TODAY(),"m")=6 and changed that font to orange. Then I clicked okay and nothing happened. In (K1) is where I have the date =Today()? Did I not do something right? "David Biddulph" wrote: First CF condition/ Formula Is/ =DATEDIF(A2,TODAY(),"y")=1 for your Red Second CF condition/ Formula Is/ =DATEDIF(A3,TODAY(),"m")=6 for Orange Default formatting Green (and perhaps also test for blank cells if this might be an option). -- David Biddulph "Melissa44" wrote in message ... I have a spreadsheet that list all my recruiter's clients and when the last time they had lunch with them. I am looking for a way to change the font color depending on when the last time they had lunch with them. Here is what I am looking for. Year and Over: Red font 6 months to a year: Orange font current date to 6 months: Green Font What would be the formulas to get these to change and keep up with the most current date (being today) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with dates | Excel Discussion (Misc queries) | |||
Conditional formatting with dates | Excel Discussion (Misc queries) | |||
Conditional Formatting - Dates | Excel Discussion (Misc queries) | |||
conditional formatting with dates | Excel Worksheet Functions | |||
Conditional Formatting & Dates? | Excel Discussion (Misc queries) |