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 Th...
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 >= )
=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."
Apologies if i'm not clear
I have 3 dates for my duration
What I want to do is to show the duration for (Start date - Delivery to LOB Date) one colour
and then
(Delivery to LOB date – End Date) a separate colour under conditional formatting throughout the applicable rows
- Thank you 🙂 going to try your suggestion now
