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,

Formula in Conditional Formatting is: =AND(AC$14>=MIN($Y15,$AA15),AC$14<=MIN($Z15,$AB15))

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

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

Thanks for the formula, can you please integrate it into the below IF formula as initially requested. The old formula you provided in bold text will need to be replaced with your new formula =AND(AC$14>=MIN($Y15,$AA15),AC$14<=MIN($Z15,$AB15))

=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

Answer 4

Edited

Hi,

Please check whether the following solution is helpful:

Formula in cell AC15 is: =IF(AND(AC$14>=MIN($Y15,$AA15),AC$14<=MIN($Z15,$AB15)),$I15,0)

NOTE

1. In the above suggested formula, please change cell reference/ranges to suit Your requirement.

2. Please drag the formula down to more rows.

3. Please drag the formula right to more columns.

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 5

Hi

I'm trying to integrate the first above formula into the existing IF formula, however it isn't working.

Can you please incorporate it into the below formula like you did initially? (view below screenshot)

**=IF(AND(AC$14>=MIN($Y15,$AA15),AC$14<=MIN($Z15,$AB15)),$I15,0),**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,