Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help!!! - logical function for someone not very logical
sing Excel 2007 - Trying to write a conditional formula for cells that have
either text and dates i.e A B C D E 1. DATE MAY 09 JUN 09 JUL09 AUG09 2. 16 Feb 09 3. Complete For B2, need formula to look at A2, if A2 = complete, then Blank, if A2 = date then difference between B2 and A2, in this case May 09 and Feb 09, if less than 3 months, then fill RED, if greater than 3 months but less than 6 months, then fill AMBER, IF greater than 6 months then fill GREEN. Is this possible? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help!!! - logical function for someone not very logical
The answer to your question is yes - but some additional info and a change in
your criteria. First: Less than 3 months red. Greater than 3 months & less than 6 amber - what if the difference IS 3 months, what colour? Second: You can't enter a date into B2 and have a formula in it at the same time! Do you intend the difference in B2 to be between B1 & A2 ? "ECH123" wrote: sing Excel 2007 - Trying to write a conditional formula for cells that have either text and dates i.e A B C D E 1. DATE MAY 09 JUN 09 JUL09 AUG09 2. 16 Feb 09 3. Complete For B2, need formula to look at A2, if A2 = complete, then Blank, if A2 = date then difference between B2 and A2, in this case May 09 and Feb 09, if less than 3 months, then fill RED, if greater than 3 months but less than 6 months, then fill AMBER, IF greater than 6 months then fill GREEN. Is this possible? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help!!! - logical function for someone not very logical
Thanks - Less than or equal to 3 months is red
For the second - meant for the difference to be between B1 and A2 for cell B2, B1 and A3 for cell B3 and so on "Ron@Buy" wrote: The answer to your question is yes - but some additional info and a change in your criteria. First: Less than 3 months red. Greater than 3 months & less than 6 amber - what if the difference IS 3 months, what colour? Second: You can't enter a date into B2 and have a formula in it at the same time! Do you intend the difference in B2 to be between B1 & A2 ? "ECH123" wrote: sing Excel 2007 - Trying to write a conditional formula for cells that have either text and dates i.e A B C D E 1. DATE MAY 09 JUN 09 JUL09 AUG09 2. 16 Feb 09 3. Complete For B2, need formula to look at A2, if A2 = complete, then Blank, if A2 = date then difference between B2 and A2, in this case May 09 and Feb 09, if less than 3 months, then fill RED, if greater than 3 months but less than 6 months, then fill AMBER, IF greater than 6 months then fill GREEN. Is this possible? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help!!! - logical function for someone not very logical
OK, clear now, so:
in B2 enter =IF(OR(A2="complete",A2=""),"",B$1-A2). Copy across and down as necessary after entering the conditional formatting. The in the Conditional Formatting window use "Use a formula to determine which cells to format" and enter as the first condition: =IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)<=3 and use colour RED For the second condition: =IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)<6 and use colour AMBER and the third condition: =IF(YEAR(B$1)=YEAR(A2),MONTH(B$1)-MONTH(A2),MONTH(B$1)-MONTH(A2)+12)=6 use colour GREEN Important you enter in the above order. Trust this helps "ECH123" wrote: Thanks - Less than or equal to 3 months is red For the second - meant for the difference to be between B1 and A2 for cell B2, B1 and A3 for cell B3 and so on "Ron@Buy" wrote: The answer to your question is yes - but some additional info and a change in your criteria. First: Less than 3 months red. Greater than 3 months & less than 6 amber - what if the difference IS 3 months, what colour? Second: You can't enter a date into B2 and have a formula in it at the same time! Do you intend the difference in B2 to be between B1 & A2 ? "ECH123" wrote: sing Excel 2007 - Trying to write a conditional formula for cells that have either text and dates i.e A B C D E 1. DATE MAY 09 JUN 09 JUL09 AUG09 2. 16 Feb 09 3. Complete For B2, need formula to look at A2, if A2 = complete, then Blank, if A2 = date then difference between B2 and A2, in this case May 09 and Feb 09, if less than 3 months, then fill RED, if greater than 3 months but less than 6 months, then fill AMBER, IF greater than 6 months then fill GREEN. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with logical function | New Users to Excel | |||
Help with logical function | Excel Worksheet Functions | |||
Logical function | Excel Discussion (Misc queries) | |||
should it be if() or another logical function? | Excel Worksheet Functions | |||
Logical Function | Excel Worksheet Functions |