Excel Magic Trick 327: Gantt Chart with Weekends and Holidays

[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]

Ok. I will do that, thank you very much

[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

[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

[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

[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