Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cell automatically turns colour??
Dear Excel users,
I have a spreadsheet containing names and dates......i would like to know if there;s any formula for the below: Let's say.......2 weeks before a due date....the cell with a name on it will turn "orange" colour.....2 week after.... on the due date....the cell will auto change to "red" colour..... we would need this notification very urgently ...... Pls advice me....Thank you very much.... |
#2
|
|||
|
|||
see conditional formatting - set up a formual comapring today() to the due date
"Kelly Lim" wrote: Dear Excel users, I have a spreadsheet containing names and dates......i would like to know if there;s any formula for the below: Let's say.......2 weeks before a due date....the cell with a name on it will turn "orange" colour.....2 week after.... on the due date....the cell will auto change to "red" colour..... we would need this notification very urgently ...... Pls advice me....Thank you very much.... |
#3
|
|||
|
|||
Sorry....im not expert in Excel....so would you mind teaching me step by step
in the formula?? please.... ..... |
#4
|
|||
|
|||
Hi!
Assume cell A1 contains the name and cell B1 contains the due date. Select cell A1 Goto FormatConditional Formatting Condition 1 From the drop down select Formula Is In the box to the right enter this formula: =AND(B1<"",B1<=TODAY()) Click the Format button Set the fill color to RED Click OK Click the Add button Condition 2 Formula IS: =AND(B1<"",B1-14<=TODAY()) Click the Format button Set the fill color to orange OK out 14 days from the due date cell A1 will turn orange and stay orange until the due date. On the due date cell A1 will turn red and stay that way. Biff "Kelly Lim" wrote in message ... Sorry....im not expert in Excel....so would you mind teaching me step by step in the formula?? please.... .... |
#5
|
|||
|
|||
Dear Biff....
I have tried your method...but then..i think there's still some slight error....when i change the due date to 2 weeks before it...it didnt change orange...but stayed red all the way....eventhough i change it to 3 weeks before the due date.... Any idea? do reply asap....Thank you very much " |
#6
|
|||
|
|||
Sorry...i think i made a mistake...the formula does works...
but then if my due date is in another page( meaning i have a link to another spreadsheet which only contains the due date inside ) ...how do i make it works changing the colour ...when the date is the next spreadsheet?? " |
#7
|
|||
|
|||
Hi!
Is the due date in another worksheet in the same workbook(file), or, is the due date in different workbook(file) ? If the due date is in the same workbook(file) but on a different worksheet then you can use the Indirect function to refer to that location. Something like this: =AND(INDIRECT("sheet2!B1")<"",INDIRECT("sheet2!B1 ")<=TODAY()) OR, you could give the cell that contains the due date a defined name: Goto InsertNameDefine Name: DueDate Refers to: =Sheet2!$B$1 Then use that name in the formula: =AND(DueDate<"",DueDate<=TODAY()) If the due date is in a different workbook(file) then you can't directly reference that cell in conditional formatting. You would have to use a helper cell to link(reference) to the due date. Then use the helper cell as the reference for the conditional format. For example: Link cell C1 to the due date that is in the workbook(file) Test.xls sheet2 B1. If the file is closed include the path: In C1: ='C:\FILES\[Test.xls]Sheet2'!B1 Then, you would just use cell C1(which now contains the due date) as the reference for the conditional format. Biff "Kelly Lim" wrote in message ... Sorry...i think i made a mistake...the formula does works... but then if my due date is in another page( meaning i have a link to another spreadsheet which only contains the due date inside ) ...how do i make it works changing the colour ...when the date is the next spreadsheet?? " |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Can I fill colour in a excel cell from a return from an "IF" test. | Excel Worksheet Functions | |||
Can an excel cell automatically change fill colors based on values | Excel Discussion (Misc queries) | |||
prevent 4/5 in a cell automatically transfer to a date format | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |