How Can You Fix PC Dll Errors Effectively?: [Solved] Conditional Format and/if formula question

Conditional Format and/if formula question

Using conditional formatting in Excel 2007, cell A1 looks up a date range and the date in cell A1 will have a background colour depending on the formatting.

1 date range in mm/dd between December 29 and December 30:
=AND(TEXT(A1,"mmdd")+0>=1229,TEXT(A1,"mmdd")+0<=1230) = colour red (for example)
Works.

1 date range between Nov 06 and Dec 20 and 1 specific date Dec 31:
=OR(AND(TEXT(A1,"mmdd")+0>=1106,TEXT(A1,"mmdd")+0<=1220),TEXT(A1,"mmdd")+0=1231) = colour blue (for example)
Works.

So from the above example, I tried to have further date ranges within the same condition, so in the example below three different date ranges with the same conditional format 'result'.
If the date in Cell A1 falls between Jan 01 to Jan 06, OR Mar 11 to Apr 27 OR Oct 01 to Nov 01, should all result = background colour orange

I tried OR:
=OR(TEXT(A1,"mmdd")+0>=0101,TEXT(A1,"mmdd")+0<=0106,TEXT(A1,"mmdd")+0>=0311,TEXT(A1,"mmdd")+0<=0427,TEXT(A1,"mmdd")+0>=1001,TEXT(A1,"mmdd")+0<=1101)

and I tried OR(AND:
=OR(AND(TEXT(A1,"mmdd")+0>=0101,TEXT(A1,"mmdd")+0<=0106),AND(TEXT(A1,"mmdd")+0>=0311,TEXT(A1,"mmdd")+0<=0427),AND(TEXT(A1,"mmdd")+0>=1001,TEXT(A1,"mmdd")+0<=1101)

But this nested formula does not resolve correctly, can you see where I am making the mistake?

Anwsers to the Problem Conditional Format and/if formula question

Download Error Fixer (Free)

I believe that what you should do is set up a table like the following out to the right of your data.
I have used Range J1:L9 (Including the comments column which is not really required and not used in the formula.)

From
To
Comments

04/30/2012
05/07/2012

05/10/2012
06/07/2012

09/03/2012
09/30/2012

12/31/2012
12/31/2012
(Single day)

04/30/2013
05/07/2013

05/10/2013
06/07/2013

09/03/2013
09/30/2013

12/31/2013
12/31/2013
(Single day)

You can then use the cell reference as actual dates for the comparison and you will finish up with only 189 characters.
You will find it a lot easier to edit the table to change the conditional format than attempting to edit the formula.
Of course if you
have a different number of conditions then editing the formula will be required.

I have tested the following formula.
Note that all references to the table must be absolute (with the dollar signs).

=OR(AND(A1>=$J$2,A1<=$K$2),AND(A1>=$J$3,A1<=$K$3),AND(A1>=$J$4,A1<=$K$4),AND(A1>=$J$5,A1<=$K$5),AND(A1>=$J$6,A1<=$K$6),AND(A1>=$J$7,A1<=$K$7),AND(A1>=$J$8,A1<=$K$8),AND(A1>=$J$9,A1<=$K$9))

Added with Edit after original post:
Enter the dates in the table in your regional date format.
I formatted them to mm/dd/yyyy but it will work equally as well with any date format.

Access the files on your computer via MY COMPUTER
Under the C: drive, access the WINDOWS folder
Select the SOFTWARE DISTRIBUTION folder, followed by the DOWNLOAD folder.
Delete any files within this download folder.
If you are unable to delete these files, open the command prompt (CMD) as the administrator, and type "net stop wuauserv" into the CMD prompt window. Press ENTER. This should allow you to delete the files from step 4.
Once the files are deleted, open and run the CMD as the administrator and type "wuauclt.exe /updatenow". Press ENTER.

Recommended Method to Fix the Problem: Conditional Format and/if formula question:

How to Fix Conditional Format and/if formula question with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer. Run it, and it will scan your computer. The junk files will be shown in the list.