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 above formula has solved my issue.

Thank you so much. I really appreciate your patience and all your help :)

Kind Regards,

Harry

Answer 3

Hi, the above formula has solved my issue.

Thank you so much. I really appreciate your patience and all your help :)

Kind Regards,

Harry

Finally. Relief :-)

Appreciate Your Patience too. It takes 2 to collaborate.

Best Wishes!

Answer 4

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,

Hi,

Did the formula that I suggested in My previous response not work?

- =IF(AND(AC$14>=MIN($Y15,$AA15),AC$14<=MIN($Z15,$AB15)),$I15,0) - in My understanding, it should have gotten the desired result.

Nevertheless, please try this formula: =IF(AND(AC$14>=MIN($Y15,$AA15),AC$14<=MIN($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)

I have simply integrated the 2 formulas.

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 5

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