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,

The bold part in the formula is the modification I have made. Please check whether it is helpful:

=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)

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 3

Hi

I've just realised the Gantt hasn't picked up the original start & end date in column Q & R but has picked up the extension dates. The grey bar should be extended from 4/10/21-18/04/21 on the Gantt?

Can I please have a formula that will include original dates and extension dates as a bar in the Gantt chart.

Cheers,

Harry

Answer 4

. . . I've just realised the Gantt hasn't picked up the original start & end date in column Q & R but has picked up the extension dates. The grey bar should be extended from 4/10/21-18/04/21 on the Gantt?

Can I please have a formula that will include original dates and extension dates as a bar in the Gantt chart.

Hi,

In Your response, You mentioned:

The grey bar should be extended from 4/10/21-18/04/21 on the Gantt? - Why till 18/04/2021? Please clarify.

Nevertheless . . please check whether the following solution is helpful:

Formula in Conditional Formatting is: =AND(MIN($Q15,$S15)<=AC$14,MAX($R15,$T15)>=AC$14)

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

The above formula will consider:

- the earliest of Original Start Date AND New Start Date

- the farthest of Original End Date AND New End Date

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!