@Combination functions to Grade timelines in Excel Gantt

Copper Contributor

@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","")

 

image002.jpg

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 >= )
Thank you :) going to try your suggestion now

@mtarler 

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
I get all that. I don't understand your statement about "...the cut off to apply to rows below..." and hence don't understand what you are having trouble with in achieving your goal.
It is always much easier if you can attach the workbook or a stripped down version of it.
You indicated that the "x" and "y" formula worked so all you had to do is apply 2 conditional formats to the whole region. 1 for the dark green and 1 for the light green.

@mtarler 

First time posting here.

 

Hi, I have attached a sample. I appreciate your patience.

 

 

@Khadd3243 no problem, that is why we volunteer.  so I didn't see any conditional formatting on the sheet so i added 2 rules.  Each was Applied to columns H:CK as shown in the conditional rules manager box below:

mtarler_0-1615839920184.png

You can also see the formula that it is using on the left.

BTW, I formatted both the text color and background color the same so all those "X" and "Y" values 'disappear'

To modify, just click the 'Edit rule...' button and you will get a box like this:

mtarler_1-1615840087894.png

you can see on the upper half that the 'Format only cells that contain' option was selected and on the lower half I specified Cell Value = "Y" as the condition.  To modify the formatting options like the text color and background color just click the Format button.

You can also see the last option on the upper half says "Use a formula to determine which cells to format" and that is the custom formula option I mentioned earlier whereby you could use the conditionals that determine if the cell should be an "X" or "Y" to DIRECTLY determine if that cell should be Dark Green or Light Green.  If you ever decide to use that, just remember to make the formula based on the upper left corner cell from the Applies To section just like the formula you made to be inside the cell before you fill down and fill right.

Hope that all helps.

@mtarler
Thanks for the response. This was very helpful.