Excel Magic Trick 327: Gantt Chart with Weekends and Holidays

See how to create a Gantt Chart that will include highlight the workdays one color and weekends and holidays another color. See the WORKDAY function to calculate the end date, and the OR, AND, and WEEKDAY functions to create the Conditional Formatting for the Gantt Cell Chart.

Category:

Science & Technology

Tags:

• Excel
• Gantt
• Chart
• with
• Weekends
• and
• Holidays
• conditional
• format
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[faargenwelsh]

hi there!

two things - first of all, thank you for your excellent tutorials. they are really great and I appreciate all the trouble you give yourself every day to create these videos.

second, I think your formula is not correct : the second argument of AND function should be only "less", not "less or equal".

I think this is why you have seven days formatted for your first six-day project ; this is probably also the answer to cidfidoutube's question.

thanks again!

[ExcelIsFun]

That is a great point! Thanks!

I actually made a subsequent video at the 48:41 minute mark:

Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples)

That solves this, but I do it by subtracting 1. I like your idea of "less than" instead of "less than or equal to"!!!

Go Team!!

[badboy4life414]

Hi mr. excel expert

You are just the best!!

I hope you can help with a small excel problems!

Given year 2009 i.e. 01/01/2009 - 31/01/2009, how do you sort in weekeends and workdays ??

Thanx alot

Best regards

Michael Issac

[ExcelIsFun]

I do not know. But no problem, try THE best Excel web site around:

mrexcel[dor]com/forum

[cidfidoutube]

Hi Mike,

I have also noticed that if you start a task on monday for a duration of 5 days, the end of your project will be monday (so a total duration of 6 days). Is there a way to correct that as if I add -1 in the formula it is only working for a duration inferior of 5 days.

Thanks in advance for your feedback

[ExcelIsFun]

Dear cidfidoutube ,

Try this video, which does not include weekends or holidays:

Excel Magic Trick #106: Gantt Chart for Daily Schedule

Otherwise, subtracting one (-1) will work if your project is always under 12 days.

--excelsifun

[badboy4life414]

Ok. I will do that, thank you very much

[ExcelIsFun]

Dear cidfidoutube,

Actually, if you download the workbook you will see that I have used the MIN function and a formula for the date that does aproximately what you want.

--excelisfun

--excelisfun

[ExcelIsFun]

Dear cidfidoutube,

Sure, you could have a start date and then a cell looking for the MAX project length (in Days), then an IF formula where the logical test is COLUMNS<=MAX project length, and the IF would put a date or a blank in.

Good video maybe in the future...

--excelisfun

[cidfidoutube]

What a great video!!! thanks

Would it be possible to come up with a way to make the `period of the project (ligne 1) a dynamic range based on the longest project?

Cedric