Forum Discussion
@Combination functions to Grade timelines in Excel Gantt
@members
recommendations will be great
I have timelines in excel with 3 different dates.
Action: I want to be able to grade the timelines to reflect the 2 differences as in line 9
- The formula I have formula which I applied conditional formatting but can get them all as green
- =IF(AND(AF$5>=$G9,AF$5<=$I9),"X","")
How do I combine following outcomes needed below so I can conditional format by Y and X
- As in line 9 manually done
- Start date - Delivery to LOB - =IF(AND(AE$5>=$G9,AE$5<=$H9),"X","")
- Then Delivery to LOB – End =IF(AND(AE$5>=$H9,AE$5<=$I9),"Y","")
 
9 Replies
- I'm not sure where these X and Y are going but guessing they are in the cells that are turning green/light green. So a) yes you can combing those conditions:
=IF(AND(AE$5>=$G9,AE$5<=$H9),"X",IF(AND(AE$5>=$H9,AE$5<=$I9),"Y",""))
but b) you also don't need to put X and Y in those cell to do the conditional formatting. Conditional formatting offers custom formula option and just use the conditional part of your IF statements:
Dark Green
if the range is AE5:AZ100
Formula is =AND(AE$5>=$G9,AE$5<=$H9) OR =(AE$5>=$G9)*(AE$5<=$H9)
then do the same for the light green conditional formatting (NOTE: you have overlap for the condition when AE5 = H9 so change 1 to < or > instead <= or >= )Hi Mtarler
so this formula gives me the separations =IF(AND(AE$5>=$G9,AE$5<=$H9),"X",IF(AND(AE$5>=$H9,AE$5<=$I9),"Y",""))
I am now struggling to show the cut off to apply to the rows below in the conditional formating. Not sure where I am getting it wrong
I want to format the cells with X different colour and the cells with Y different colour- Sry you've lost me. What do you mean "I am now struggling to show the cut off to apply to the rows below in the conditional formating."
- Thank you 🙂 going to try your suggestion now
