Gantt chart to include Extension Date columns

Question

Hi Team

I've been asked to include 2 new date ranges into my Gantt chart. I've inserted helper columns where IF formulas feed off to populate the gantt view.

The 2 new helper columns are in column Y & Z and im trying to include these date ranges into the below if Formula;

=IF($AA15<=AC$14,IF($AB15>=AC$14,IF($I15="Employee (perm/fixed term/internal)","Employee (perm/fixed term/internal)",IF($I15="Contractor","Contractor",IF($I15="New Recruit - External","New Recruit - External",IF($I15="SOW (Statement of Work)","SOW (Statement of Work)",IF($I15="New Recruit - Perm","New Recruit - Perm",0))))),0),0)

I want to have these 2 new dates included into the above formula so I can capture extension dates into my Gantt.

Any help here would be greatly appreciated.

Cheers,

Harry

Answer 1

Hi

Your initial formula doesn't factor in the other IF statements I want included in the overall formula.

The second formula you provided is working, however isn't capturing the full date range, it still seems to stop at the original end date 28/03/22, where it should stop at the furthest date which in this case is the Extension end date of the 26/09/22...?

Cheers,

Harry

Please try the following formulas:

1. In Conditional Formatting: =AND(AC$14>=MIN($Y15,$AA15),AC$14<=MAX($Z15,$AB15))

2. =IF(AND(AC$14>=MIN($Y15,$AA15),AC$14<=MAX($Z15,$AB15)),IF($I15="Employee (perm/fixed term/internal)","Employee (perm/fixed term/internal)",IF($I15="Contractor","Contractor",IF($I15="New Recruit - External","New Recruit - External",IF($I15="SOW (Statement of Work)","SOW (Statement of Work)",IF($I15="New Recruit - Perm","New Recruit - Perm",0))))),0)

Hope, it solves Your question.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My responses as answer and helpful.

Thank You!

Answer 2

Hi,

Apologies that was a typo, it was meant to be from the 4/10/21-18/04/22

if I want the above formula to reflect off the helper columns and to be integrated into the below formula which is for the Gantt

=IF(MAX($Y16,$AA16)<=AC$14,IF(MAX($Z16,$AB16)>=AC$14,IF($I16="Employee (perm/fixed term/internal)","Employee (perm/fixed term/internal)",IF($I16="Contractor","Contractor",IF($I16="New Recruit - External","New Recruit - External",IF($I16="SOW (Statement of Work)","SOW (Statement of Work)",IF($I16="New Recruit - Perm","New Recruit - Perm",0))))),0),0)

I've provided a screenshot of the columns, if you can please provide an updated formula, that'll be greatly appreciated.

Cheers,

Harry

Answer 3

Hi,

Formula in Conditional Formatting is: =AND(MIN($Q16,$S16)<=AC$14,MIN($R16,$T16)>=AC$14)

- If required, in the above formula, please modify cell references to suit Your requirement.

To highlight the Gantt Chart, it is not required to consider the helper columns.

Hope, this answers Your question.

If it does not, please share all the information + screenshot of the expected result (You may manually highlight the cells in the Gantt Chart).

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 4

Hi there,

I need a formula that references off the helper columns because the original IF formula feeds off them to identify the different Resource Types with conditional formatting built in to change colour based on the different resource types and dates.

The helper columns identifies the starting Monday of that week that references off the below 4 columns.

I would like the formula to consider the:

- the earliest of Start Date Original Monday in cell AA15 AND Extension Start Date Monday in Y15

- the farthest of End Date Original Monday in cell AB15 AND Extension End Date Monday in Z15

I want it to be integrated into the below formula with the below expected result (I've highlighted the cell range that should be updated in grey(;

=IF(MAX($Y15,$AA15)<=AC$14,IF(MAX($Z15,$AB15)>=AC$14,IF($I15="Employee (perm/fixed term/internal)","Employee (perm/fixed term/internal)",IF($I15="Contractor","Contractor",IF($I15="New Recruit - External","New Recruit - External",IF($I15="SOW (Statement of Work)","SOW (Statement of Work)",IF($I15="New Recruit - Perm","New Recruit - Perm",0))))),0),0)

Cheers,

Harry