| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format based on date ranges
I am trying to set the following to have conditional formatting:
Current date < Current date+14 days = Green shade Current date+15<Currentdate+30=Yellow shade <=Current date=Red shade I am having trouble with the Between formulas. I have tried something like: Cell value is BETWEEN: [NOW()] and [NOW()+14] Green shade Cell value is BETWEEN: [NOW()+15] and [NOW()+30] Yellow shade Cell value is GREATER THAN: [NOW()+31] Red shade But i get no CF? The values ARE a calculated DATE value, Does this mean i should use the FORMULA IS instead of the Cell is? I then ONLY get Value is and Not the option of IS BETWEEN.... Any ideas i am sure it is a simple formula sintax error i am doing wrong Regards Corey |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format based on date ranges
The order in which you apply the conditions is important. You can use "formula is" but "cell value is" works fine too. Use something like this condition 1 "cell value is" Greater than =TODAY()+30 red format condition 2 "cell value is" Greater than =TODAY()+14 yellow format condition 3 "cell value is" Greater than =TODAY() green format -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=566469 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format based on date ranges
Thanks for the reply.
I have entered as you suggested, but i do not get ANY shading at all?? -- Regards Corey "daddylonglegs" wrote in message news:daddylonglegs.2bs6qv_1154301011.7433@excelfor um-nospam.com... The order in which you apply the conditions is important. You can use "formula is" but "cell value is" works fine too. Use something like this condition 1 "cell value is" Greater than =TODAY()+30 red format condition 2 "cell value is" Greater than =TODAY()+14 yellow format condition 3 "cell value is" Greater than =TODAY() green format -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=566469 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format based on date ranges
Here are the formulas you need. I made a couple of adjustments. Day 14 & 15
get overlooked according to your formulas and if that is what you want, then adjust the formulas I send you. I am using A1 as the cell with the date you want to check: 1st) =AND(A1<"", A1TODAY(), A1<=TODAY()+14) 2nd) =AND(A1<"", A1TODAY()+14, A1<=TODAY()+30) 3rd) =AND(A1<"", A1<=TODAY()) "Corey" wrote: I am trying to set the following to have conditional formatting: Current date < Current date+14 days = Green shade Current date+15<Currentdate+30=Yellow shade <=Current date=Red shade I am having trouble with the Between formulas. I have tried something like: Cell value is BETWEEN: [NOW()] and [NOW()+14] Green shade Cell value is BETWEEN: [NOW()+15] and [NOW()+30] Yellow shade Cell value is GREATER THAN: [NOW()+31] Red shade But i get no CF? The values ARE a calculated DATE value, Does this mean i should use the FORMULA IS instead of the Cell is? I then ONLY get Value is and Not the option of IS BETWEEN.... Any ideas i am sure it is a simple formula sintax error i am doing wrong Regards Corey |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format based on date ranges
The following works for me
Select the first cell (calculated or not - it makes no difference) for the conditional formatting Format Conditional Formatting Condition 1 <Cell Value between =now()-1 and =now()+14 format to required green Add condition 2 <Cell Value between =now()+15 and =now()+30 format to required yellow Add condition 3 <Cell Value greater than =now()+30 format to required red (Note that in the conditions there are no spaces) OK Click on FORMAT PAINTER Highlight all the cells the formatting is to apply to Hope this solves your problem Don C "Corey" wrote: Thanks for the reply. I have entered as you suggested, but i do not get ANY shading at all?? -- Regards Corey "daddylonglegs" wrote in message news:daddylonglegs.2bs6qv_1154301011.7433@excelfor um-nospam.com... The order in which you apply the conditions is important. You can use "formula is" but "cell value is" works fine too. Use something like this condition 1 "cell value is" Greater than =TODAY()+30 red format condition 2 "cell value is" Greater than =TODAY()+14 yellow format condition 3 "cell value is" Greater than =TODAY() green format -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=566469 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional format by comparing to current date | Excel Worksheet Functions | |||
| Changing date format in a footer | Excel Discussion (Misc queries) | |||
| Date Format Problems?? | Excel Discussion (Misc queries) | |||
| Conditional Format based on contents of cell. | Excel Worksheet Functions | |||
| How do I do conditional format based on a cell with a formula? | Excel Worksheet Functions | |||
Linear Mode
