Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Hi,
Here is my problem, I have a list of dates of when a product was shipped in column C and the date when it was received in column D. I would like the date in D to be highlighted red when it is more than 3 working days from the date in C and green when it's less than or equal to 3 working days. I would also like the rest of the cells in D to be white until a date is entered. Any help and advice that you can offer would be appreciated. Thanks, Gav. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
You need 2 conditional formats in D
First. Cell value is - less than or equal to =$C$2+3 and set the colour to green Click ADD Second. Cell value is - Greater than =$C$2+3 and set the colour to red. Mike "Gav123" wrote: Hi, Here is my problem, I have a list of dates of when a product was shipped in column C and the date when it was received in column D. I would like the date in D to be highlighted red when it is more than 3 working days from the date in C and green when it's less than or equal to 3 working days. I would also like the rest of the cells in D to be white until a date is entered. Any help and advice that you can offer would be appreciated. Thanks, Gav. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Hi Mike,
Thanks for the quick response. I need the formatting to work on week days only as we don't include the weekends. Gav. "Mike H" wrote: You need 2 conditional formats in D First. Cell value is - less than or equal to =$C$2+3 and set the colour to green Click ADD Second. Cell value is - Greater than =$C$2+3 and set the colour to red. Mike "Gav123" wrote: Hi, Here is my problem, I have a list of dates of when a product was shipped in column C and the date when it was received in column D. I would like the date in D to be highlighted red when it is more than 3 working days from the date in C and green when it's less than or equal to 3 working days. I would also like the rest of the cells in D to be white until a date is entered. Any help and advice that you can offer would be appreciated. Thanks, Gav. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Gavin,
In a cell (say E1) alongside your first row enter the formula =SUM(INT((WEEKDAY(C1-{2,3,4,5,6})+D1-C1)/7)) The will calculate the workdays beytween c1 and d1 Now enter two conditional formats. 1. =E1<=3 and set green 2. =E1 3 and set Red You can drag down the formula in E1 for additional cells. Mike "Gav123" wrote: Hi Mike, Thanks for the quick response. I need the formatting to work on week days only as we don't include the weekends. Gav. "Mike H" wrote: You need 2 conditional formats in D First. Cell value is - less than or equal to =$C$2+3 and set the colour to green Click ADD Second. Cell value is - Greater than =$C$2+3 and set the colour to red. Mike "Gav123" wrote: Hi, Here is my problem, I have a list of dates of when a product was shipped in column C and the date when it was received in column D. I would like the date in D to be highlighted red when it is more than 3 working days from the date in C and green when it's less than or equal to 3 working days. I would also like the rest of the cells in D to be white until a date is entered. Any help and advice that you can offer would be appreciated. Thanks, Gav. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Thanks Mike..Worked like a charm...
"Mike H" wrote: Gavin, In a cell (say E1) alongside your first row enter the formula =SUM(INT((WEEKDAY(C1-{2,3,4,5,6})+D1-C1)/7)) The will calculate the workdays beytween c1 and d1 Now enter two conditional formats. 1. =E1<=3 and set green 2. =E1 3 and set Red You can drag down the formula in E1 for additional cells. Mike "Gav123" wrote: Hi Mike, Thanks for the quick response. I need the formatting to work on week days only as we don't include the weekends. Gav. "Mike H" wrote: You need 2 conditional formats in D First. Cell value is - less than or equal to =$C$2+3 and set the colour to green Click ADD Second. Cell value is - Greater than =$C$2+3 and set the colour to red. Mike "Gav123" wrote: Hi, Here is my problem, I have a list of dates of when a product was shipped in column C and the date when it was received in column D. I would like the date in D to be highlighted red when it is more than 3 working days from the date in C and green when it's less than or equal to 3 working days. I would also like the rest of the cells in D to be white until a date is entered. Any help and advice that you can offer would be appreciated. Thanks, Gav. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Please help with Conditional Formatting | Excel Discussion (Misc queries) |