Excel Magic Trick 564: Daily Gantt Chart

[lonnix007]

Excellent video. Many THANKS.

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

[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 do not know how to do that.

[ipacman2012]

Mike, I like the look of this but !oops! the gantt doesn't calculate the finish date correctly if the start date is a (non working day) Saturday, Sunday or Monday is this case.

Is there is a way to allow for this?

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

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

Cool!

[JJtoob]

What about when there are two start and end times within the same day? Like when there is a break in a day or week, or whatever lenght of time the x axis covers?

[ExcelIsFun]

It would be a completely different set of calculations. I'll do a video in the next week. Watch for it.

[misterb101]

Great video! thanks

One question: when you make a selection and type in a formula it repeats it for every selected cell, very handy! In my Excel 2008 (Mac) when I make a selection and type in a value or formula, it only does it for the active cell. Can you tell me if this is a setting I can change or am I doing something wrong?

[ExcelIsFun]

I have no idea how to use a Mac (even though Macs are better and even though in the early days of my computer use I only used a Mac, eventially I had to switch becasue I was working in the business world).

Why they make a different Excel for the Mac is beyond me. I wish they would just be the same...

Sorry.

[krn14242]

Mike, thanks for being a great teacher. Your message is clear, practiced, and structured. Meets the "Tell, tell, tell" criteria.

[ExcelIsFun]

I am glad that it helps effectively!

What is "tell, tell, tell" criteria?

[krn14242]

Tell them what you are going to say, Tell them, and tell them what you told them. Overview, Body, Summary.

[ExcelIsFun]

Cool! Got it!

[shiryu008]

Hi Mike.

related to schedules. Let consider i have in one cell a value like 1500 hours, ok? This value a i want to spread it from a starting date to an ending date on the cells. The first idea is to devide the 1500 by the number of cell you have fron start to finish and the sum of the cell would be 1500. However i want the value of the cell to increase gradually like S curve. would like to help me please.

Always loving you videos.

[ExcelIsFun]

I do not know, try posting your question to the Mr Excel Message Board (must post clear question):

mrexcel[dor]com/forum

[msanintl]

Dear Mike,

I am Ahmed from Pakistan,Thanks for all your online videos, you are really AWESOME ! ! !

I learned a lot about Excel which about I was not aware of, I must say that you are True Excel Guru and your method of teaching is Great... every body can learn easily with help of your videos...

Keep it up and keep us updated with your new tricks and tips...

You have my good wishes always..

Regards

Ahmed

[ExcelIsFun]

I am glad that the videos are helpful! But I am no Excel Guru, just a guy having fun with Excel!!

[planiolro]

I agree totally awesome!

[planiolro]

I agree totally awesome!

[planiolro]

I agree totally awesome!

[ExcelIsFun]

Woo Hoo!!!