Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Conditioning Formatting Based On Due And Overdue Dates
I'm trying to set up a sheet where if a date is inputted in Cell A, it would
generate a due date in Cell B, and a date for actual completion would have to be entered in Cell C. I'm trying to set it up so that if the Cell C is blank, and it is past the due date, then the date in Cell B would be in red. Also, how do you set Cell B up to stay blank (instead of having a date with the year 1900 in it) if nothing has yet been entered in Cell A? -- Israel Rodriguez http://home.earthlink.net/~isrodriguez7/ |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Conditioning Formatting Based On Due And Overdue Dates
Israel
first, how to "set Cell B up to stay blank": =IF(A2="","",A2+30) so, if A2 is blank, then B2 is blank, otherwise add 30 to the date in A2 (format as a date) and for the conditional formatting: change the condition to "Formula is" and set the value to =AND($B2=NOW(),$C2="") Regards Trevor "Israel Rodriguez" wrote in message ... I'm trying to set up a sheet where if a date is inputted in Cell A, it would generate a due date in Cell B, and a date for actual completion would have to be entered in Cell C. I'm trying to set it up so that if the Cell C is blank, and it is past the due date, then the date in Cell B would be in red. Also, how do you set Cell B up to stay blank (instead of having a date with the year 1900 in it) if nothing has yet been entered in Cell A? -- Israel Rodriguez http://home.earthlink.net/~isrodriguez7/ |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Conditioning Formatting Based On Due And Overdue Dates
"Trevor Shuttleworth" wrote in message ... Israel first, how to "set Cell B up to stay blank": =IF(A2="","",A2+30) so, if A2 is blank, then B2 is blank, otherwise add 30 to the date in A2 (format as a date) and for the conditional formatting: change the condition to "Formula is" and set the value to =AND($B2=NOW(),$C2="") Regards Trevor The first formula works fine, but I'm having trouble with the second formula. -- Israel Rodriguez http://home.earthlink.net/~isrodriguez7/ |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Conditioning Formatting Based On Due And Overdue Dates
Israel
"I'm having trouble with the second formula." And that would be ... ? Activate cell B2. Then select Format | Conditional Formatting... Change the first box from "Cell value is" to "Formula is" and in the second box put the formula: =AND($B2=NOW(),$C2=""). Now click the format button and set the formatting for the cell as you wish, perhaps font colour = red. Regards Trevor "Israel Rodriguez" wrote in message hlink.net... "Trevor Shuttleworth" wrote in message ... Israel first, how to "set Cell B up to stay blank": =IF(A2="","",A2+30) so, if A2 is blank, then B2 is blank, otherwise add 30 to the date in A2 (format as a date) and for the conditional formatting: change the condition to "Formula is" and set the value to =AND($B2=NOW(),$C2="") Regards Trevor The first formula works fine, but I'm having trouble with the second formula. -- Israel Rodriguez http://home.earthlink.net/~isrodriguez7/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to create an overdue message based on set dates | Excel Discussion (Misc queries) | |||
Conditioning formatting | Excel Worksheet Functions | |||
Conditioning Formatting - Thicker Borders | Excel Worksheet Functions | |||
conditional formatting overdue dates | Excel Discussion (Misc queries) | |||
conditional formatting overdue dates | Excel Discussion (Misc queries) |