Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting in a worksheet
Hi,
I have a list of items and each row has a cell with an exipiration date. (I have no idea how hard or easy this is) I would like to format the date cell so that if the date entered will be coming up in 3 months or less, the cell gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06, Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see that the expiration date is less than 3 months away. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting in a worksheet
Hi Andy,
try on the conditional format the cell value is | between | =a1 | and | =a1+90 hth regards from Brazil Marcelo "Andy Shapiro" escreveu: Hi, I have a list of items and each row has a cell with an exipiration date. (I have no idea how hard or easy this is) I would like to format the date cell so that if the date entered will be coming up in 3 months or less, the cell gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06, Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see that the expiration date is less than 3 months away. Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting in a worksheet
Use a formula of
=AND(B2=TODAY(),B2<=DATE(YEAR(TODAY()),MONTH(TODA Y())+3,DAY(TODAY())) and copy the format across -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Andy Shapiro" <Andy wrote in message ... Hi, I have a list of items and each row has a cell with an exipiration date. (I have no idea how hard or easy this is) I would like to format the date cell so that if the date entered will be coming up in 3 months or less, the cell gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06, Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see that the expiration date is less than 3 months away. Can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting in a worksheet
Select the cell with the date in it
Go to Conditional formatting Use Cell Value - Less than - =today()+90 and format with a colour ( I would select red) Set up a second condition Cell Value - Equals - =today()+90 and format with a colour (say blue) Set up a third condition Cell value - greater than - =today()-90 and format with a colour ( say green) Now with this cell selected use format painter and apply to all the cells where this test is required. You will then find that all dates less than 90 day will be highlighted in red, exactly 90 days will be blue and greater than 90 days will be green Hopefully this is exactly what you want Cheers -- Don C "Andy Shapiro" wrote: Hi, I have a list of items and each row has a cell with an exipiration date. (I have no idea how hard or easy this is) I would like to format the date cell so that if the date entered will be coming up in 3 months or less, the cell gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06, Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see that the expiration date is less than 3 months away. Can anyone help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting in a worksheet
Thank you to all! I'll try them out right now!
"DonCam65" wrote: Select the cell with the date in it Go to Conditional formatting Use Cell Value - Less than - =today()+90 and format with a colour ( I would select red) Set up a second condition Cell Value - Equals - =today()+90 and format with a colour (say blue) Set up a third condition Cell value - greater than - =today()-90 and format with a colour ( say green) Now with this cell selected use format painter and apply to all the cells where this test is required. You will then find that all dates less than 90 day will be highlighted in red, exactly 90 days will be blue and greater than 90 days will be green Hopefully this is exactly what you want Cheers -- Don C "Andy Shapiro" wrote: Hi, I have a list of items and each row has a cell with an exipiration date. (I have no idea how hard or easy this is) I would like to format the date cell so that if the date entered will be coming up in 3 months or less, the cell gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06, Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see that the expiration date is less than 3 months away. Can anyone help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting in a worksheet
Don,
I loved your idea. Absolutely PERFECT! I changed the formatting slightly to Red, Yellow, Green. Again, Thanks to al! "DonCam65" wrote: Select the cell with the date in it Go to Conditional formatting Use Cell Value - Less than - =today()+90 and format with a colour ( I would select red) Set up a second condition Cell Value - Equals - =today()+90 and format with a colour (say blue) Set up a third condition Cell value - greater than - =today()-90 and format with a colour ( say green) Now with this cell selected use format painter and apply to all the cells where this test is required. You will then find that all dates less than 90 day will be highlighted in red, exactly 90 days will be blue and greater than 90 days will be green Hopefully this is exactly what you want Cheers -- Don C "Andy Shapiro" wrote: Hi, I have a list of items and each row has a cell with an exipiration date. (I have no idea how hard or easy this is) I would like to format the date cell so that if the date entered will be coming up in 3 months or less, the cell gets highlighted as a flag. As an example: Cell B2 has a date of 11/2/06, Cell C2 has a date of 8/2/06. Cell C2 should be highlighted so that I see that the expiration date is less than 3 months away. Can anyone help? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting in a worksheet
Thank you Andy
Pleased I could help -- Don C "Andy Shapiro" wrote: Don, I loved your idea. Absolutely PERFECT! I changed the formatting slightly to Red, Yellow, Green. Again, Thanks to al! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional Formatting: "handwritten" circles? | Excel Discussion (Misc queries) | |||
Conditional Formatting - Formulas | Excel Discussion (Misc queries) | |||
Conditional formatting is unavaiable in worksheet, what can I do? | Excel Worksheet Functions | |||
Conditional Formatting | Excel Discussion (Misc queries) |