Excel Magic Trick 564: Daily Gantt Chart

See how to create a cell chart using conditional formatting with Logical TRUE FALSE formulas to create a Gantt Chart. See the functions WORKDAY, AND, NOT, NETWORKDAYS

Category:

Science & Technology

Tags:

• Excel
• 2007
• Daily
• Gantt
• Chart
• the
• functions
• WORKDAY
• AND
• NOT
• NETWORKDAYS
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• function

[mohamedmeshref]

Can we make different colors for weekends & Holidays?

[ExcelIsFun]

In this example, this should highlight holidays only;

=AND(D$2>=$A3,D$2<=$C3,ISNUMBE­R(MATCH(D$2,$A$11:$A$16,0)))

[mohamedmeshref]

@ExcelIsFun

I need to highlight both weekends & Holidays but with different colors.

Dr. Mohamed Meshref, Egypt

[ExcelIsFun]

With the other formulas in the video, just add the one I suggested for thr weekend.

[lonnix007]

Excellent video. Many THANKS.

[ExcelIsFun]

Cool!

[mohamedmeshref]

@ExcelIsFun

You can calculate the END DATE using the following Formula:

=IF(NOT(ISNA(VLOOKUP(WEEKDAY(A­3),$C$11:$C$12,1,0))),WORKDAY(­A3,B3,$A$11:$A$16),IF(NOT(ISNA­(VLOOKUP(A3,$A$11:$B$16,1,FALS­E))),WORKDAY(A3,B3,$A$11:$A$16­),WORKDAY(A3,B3-1,$A$11:$A$16)­))

By this way, it does not matter, if you start with a week end day or even a holiday. The idea is that you don't subtract ONE from the working days if you start with a weekend or a holiday

Best regards

Dr. Mohamed Meshref

[ExcelIsFun]

I do not know how to do that.

[kemba7]

Hey buddy, are you able to do a formula to prevent the task from splitting? meaning to say the green area will appear behind the red zone if the uncolored area do not have enough days to complete the task.

[ExcelIsFun]

I think it does work, but in the video and the workbook I mistakenly included a start date that is a weekend. If you don't type weekends and holidays in the start date cell, it looks like it works ... Thanks for the heads up. I will post an annotation to let people know that I mistakenly added the wrong dates in the start cell.