TECH-005 - Create a quick and simple Time Line (Gantt Chart) in Excel
HTML-код
- Опубликовано: 25 апр 2017
- YOU CAN DOWNLOAD ALL MY FILES FOR FREE AT www.theexcelchallenge.com
Learn how to create a quick and simple Time Line (Gantt Chart) in Excel.
In this video you will use the MIN formula, the MAX formula, the IF formula, the AND formula, as well as 'Greater Than' and 'Less Than' conditions to create the Time Line (Gantt Chart) in your spreadsheet. You will also use Conditional Formatting to enhance the looks of your Time Line.
I hope this tool is something you can use in your daily routine so you get more with less effort.
If you have a challenge with Excel that you need help with, whether it is at work, at home, or at school, please write it in the comments below. I try to resolve common Excel challenges from our viewers and subscribers.
Thank you for watching.
Check out some of our previous videos in the links below:
* TECH-004 - Calculate total days between dates in Excel
• TECH-004 Calculate tot...
* TECH-003 - Display images in comments
• TECH-003 - Insert imag...
* TECH-002 - Find intersecting values in a table in Excel
• TECH-002 - Find a valu...
Man you've explained it smoothly with literal ease. This is by far one of the easiest video to follow and understand thoroughly. Great job!
Dude, First of all, I would like to thank you for your hard work, but most importantly, I would like to thank you for making all your spreadsheets available for free. My life has been made so much easier and more efficient by this.
You have no idea how much this helped me! This is exactly what I needed to create a super effective timeline for my project. Thank you very much! Needless to say, I am a new subscriber.
I just want to say Thank you. I was doing my assignment and it needed a Gantt chart i didn't even know where to start , i followed your tutorial and i must say it is outstanding. you a life saver.
The easiest and best explanation I've seen on how to set up a Gantt Chart. Thank you TECH.
Hello Sophia. Thank you very much for your kind note. It is really nice to hear this type of comments from viewers and subscribers. I invite you to check out some of the other videos I've posted in my channel. Thanks for watching.
This was SO helpful! Thank you so much for taking the time to make this video. Your tips, formulas, layout, etc. were all really easy to follow. I really appreciate it!
This video has changed my life!!! Thank you so much! ❤️🥰❤️
This is an excellent tutorial on how to perform a gnatt chart that can be used to track the dates for your project and see its urgency. I did one similar to yours according to my start and end dates and its perfect. Thanks for this great information.
I must say one of the best teaching videos on RUclips, i thank you so much.
I've been trying to find a good tutorial for ages and I stumbled on this and it was just what I needed! Thank you! :)
Same here i was also searching searching gantt charts and always lost.
Take a look this one : ruclips.net/video/un8j6QqpYa0/видео.html
I had an idea to make a timeline for classical music 5 minutes ago. Now I’m here.
I am always looking for a fast Gantt chart template that I can use to keep control of bad customer expectations. This is one of my favorites. Thanks again for sharing this!
Very helpful Tutorial. Explained the concept in a very easy to understand manner. I am an Architect and was looking for a tutorial to help me schedule my projects in execution and this tutorial has exactly what I was looking for. Thanks for this. Keep up the good work...
Ditto but I am Mechanical Engineer
It is the same for me. I am a project manager responsible for curriculum for an online educational provider and this tutorial was perfect - thank you
Really well explained! Thanks TECH
Thanks for you comment Marce and thanks for watching. Stay tuned for more videos in our channel.
Thank you very much, this is what I was looking for. Saved me. Thumbs up!
A quick thank you - you posted this a few years ago - but it remains very helpful!
Man, 10 years I worked on the construction time schedule, recently I saw Excel doing automatic things, I think it can only be in MS Project, rock you man..
Benar baget pak
you are a true Excel wizard. it's the real thing.
Thank you Andrew and thanks for watching.
That was well done, sir! You explained that process perfectly. Thank you very much for sharing. 😀
You helped me at work. Thank you! Been trying to do this for hours!!!
Great stuff mate. ;) Really useful video using all the formulas and automated calculations potential embedded in Excel. Had no idea we could be so creative with formulas in Excel. You're a star. 👍🌟
Exactly what I needed, thank you so much!!!
Oh my gosh! This was a very helpful tutorial! It walked me through creating my own timelilne. Thank you. I just wish I knew more about these types of formulas so that I wouldn't have to be walked through one step by step.
This was an excellent step by step demo.
I have a lot of users who do not have Project so they can't access Gannt charts, this was my alternative. Thanks!
thanks a million! I'd been looking for an understandable tutorial of how to do a Gantt Chart and you just made such a great video that anyone can understand how to creat a chart, eventhough they don't have a clue of how to use excel like in my case haha...
Great work. There are now a few good Gantt Chart templates available on exel for those of us less skilled at formating.
I just love you man. I learnt excel(Conditional formating, conditional formula) and Gantt chart together. Kudos to you. Keep uploading these amazing stuffs
Thank you so much, you helped me prepare an end minute project plan quickly. Thoroughly appreciate this post!!
Oh my goodness you’re a life saver!!!
I needed to get a project done in 1 hour😆
Your comment said it all😆😆
Thank you so much for this! It has helped me complete my dissertation for final year of University
Thank you so much. You’ve saved so many poor students and newbies with this tutorial.
You are fabulous - thanks so much for this detail explanation on how to create a Gantt Chart in Excel.
Amazing, thank you so much, this really helped me a lot
Excellent video content! Apologies for butting in, I would love your opinion. Have you tried - Weydaniel Organized Dominator (google it)? It is a smashing one off product for getting 7000 plus project management and business templates without the hard work. Ive heard some unbelievable things about it and my mate after many years got amazing success with it.
Amazing, thank you so much, this really helped me a lot, stay blessed
I got mixed up looking for Gantt charts tutorial, this video of yours really got me what i need...thank u a lot for making life so easy.
I love this tutorial! Can you think of an easy way to modify this so that some of the tasks could run concurrently?
Many thanks, I've been looking for "project management resource plan template" for a while now, and I think this has helped. Have you heard people talk about - Seyily Substratal Smasher - (do a search on google ) ? It is an awesome one off product for discovering how to get amazing project management and business templates without the headache. Ive heard some great things about it and my colleague got excellent results with it.
When locking a cell, you can also use F4 to cycle through the vertical, horizontal and both locks.
How? It's not working for me
REMARKABLE! Huge help, thanks for your effort!
Exactly what I was looking for, thank you!
Really wonderful video. I found that the end date should require adjustment.
I guess the end date should be start date + Task Duration - 1.
SIR, If I learn most of the project management tool on excel along with good experience (3 years) in engineering drafting for which profile & position I can give try?
Project Manager
start date + Task Duration - 1 that mean it includes start date
@@harshadkondalkar1502 Thanks for your comment, I find this helpful.
@@justinbancayrin3549 as always
Hi, how to skip certain date?
Can u teach me how to use the formula.. ty
SUPER HELPFUL!! Straight to the point and easy to follow along and with a great example! Thank you so much!!! SUBSCRIBED!
Thank you very very much and this helps me a lot.
I have prepared my own Gantt chart by simply following your instructions.
Thank you very much.
Great explaination, easy and simple. Really making excel easy. Please could you kindly provide small list for all formulas used. e.g The IF in comparation.
Million thanks. It was helpful
Hello Sakah. Thanks for your comment and thanks for subscribing. I hope you enjoy the other challenges I've posted.
Here is the list of formulas I used in this video: MIN, MAX, IF, AND, and also Greater Than, Less Than (really simple formulas). I find that the key in Excel is to making good combinations of simple formulas and other tools. You will see that in my other videos.
Stay tuned for more videos Sakah.
How do I lock
A real good and inspiring tutorial; this is very useful. I struggled with similar challenges. Calculating with durations and dates is a bit tricky. Could it be that for calculating the end date you have to add the duration minus one (i.e. =D6+C6-1)? A one-day job has then the same start and end date. Apart from that really great and very useful! Thank you very much.
Agree. Have to add one
Yes, I too realized that the days considerations are incremented by one.
PERFECT , and I finished my project with in a day before final submission, thanks
What a fabulous video! A million stars! The website with templates is also super useful!
Great video. The most helpful one I've seen. However, how do I block out weekends and holidays?
You make list of holidays in separate sheet and use workday function which will automaticly exclude those days. Also if you have project going on in countries that have other weekend days (friday & saturday) keep that on mind.
Like it a lot,
Can you make the timeline based on the weeks of the year?
THank you!! this is the best and most simplistic gantt I've found...I'm using to create a roadmap.
This is really helpful. I made exactly the same project details using your video. Thank you very much. I recommend it anyone who really wants to make a time table or revision.
Very helpful tutorial!
Question: how can I integrate into the formula to leave blank the timeline when the date falls in a saturday and sunday?
You could do that by modifying two of his formula, using Excel functions WEEKDAY() and WORKDAY(). It's not hard to do, and takes two steps.
STEP ONE
========
First, you'd add something to the formula used in the timeline cells (the one he starts building it at 5:10) to prevent the green blocks appearing on the weekend. So his formula looks like this:
=IF(AND(F$5>=$D6,F$5
@@bakedutah8411 It worked for saturdays but not for sundays. I still have the green squares on SUNDAY. :( How can I take it away?
Marskocoro, oops, I had a typo. My check for not-Sunday (the big formula in STEP ONE) had 71 instead of just 1. It’s now fixed.
@@bakedutah8411 Great work !! Immense help...
But it will be the best formula if you can help me include holidays too..what syntax, formula to add as logical test if i have a table array/ list of national holidays that i dont want the timeline tracker to add ??
Please help..am stuck and it is a work-related issue...
Looking forward to your tip :)
Ateesh Hazra, the WORKDAY() function has an optional third argument - a list of days to ignore, oftend used for holidays. So just add the range where your national holidays are, and that will take them into account. More here: support.office.com/en-us/article/workday-function-f764a5b7-05fc-4494-9486-60d494efbf33
This was a very helpful video, I really like it. My question is "How would you create a gantt chart that considers tasks that start on the same day as other tasks without affecting the timeline?"
Hello Joshua. It's nice to hear you liked the video and that you found it helpful.
To answer your question, look at this video as an example only, and use it as a foundation for your project, which you will customize per your own needs (refer to my note in 2:40 min mark)
For example, you don't have to add +1 day to the end date of the previous task (2:54 min).
You can simply make it = equals. Or if you want, you can simply punch in the start date.
Give it a try, and use the video as a reference only. Your project will meet your needs much better.
Thank you for watching and come back often to check out more videos.
Man... This is awesome. I know there are a ton of tools out there, and I even use one myself, I use Notion for it, but knowing and understanding this is gold.
Thanks a lot.
Thank you so much, this was a fantastic quick and accurate tutorial. Have allllll of my thumbs up! 👍🏻
end date will be D6+C6-1 ex: may 1-2-3-4 duration will be 4 day
How the formulae change if I have half a day for a specific task ?
Do I have to use per hour calculations ?
Thankyou so far,
but I would like to consider the 2 day weekend where no work can be carried out
You need to use the workday function. Instead of +1 (at 8:26) you use =workday(G5,1). This will automatically calculate out the weekends. You also have the added benefit of figuring in holidays that need to be excluded.
This was extremely helpful. Thank you!
I must admit I was super excited watching you video as I have been looking for something like this for some time now, Then when I saw the work that went behind creating knowing how bad I am with excel- I thought I just wish he can just share that template. And then, there it is the first thing in your description... Thank you very very much for this, You made this lady very happy...
Thanks so much! How do you set the work week for MON-FRI?
You need to use the workday function. Instead of +1 (at 8:26) you use =workday(G5,1). This will automatically calculate out the weekends. You also have the added benefit of figuring in holidays that need to be excluded.
@@Daeon108 Hi Daeion, this works perfectly! but now its doesnt count all the days - ex if there are 17 workingdays only 5 blocks are shown and it doesnt continue after the weekend - do you have a solution for this please?
Thanks, it was helpful. Could you please advise how to deal with weekend when no task activity will happen.
Hello Vaibhav, I'm glad you found this video helpful.
In regards of weekends, use the recommendation I put in the example but customize per your needs. For example, in the top of the table where I put the whole 31 days of the month, simply enter weekdays and skip weekends. Give it a try and that should help. Let me know if you need more help.
For example, if you want to use July of 2017, your top header would show July 3, 4, 5, 6, 7, 10, 11, 12, 13, 14, 17, 18, 19, 20, 21, 24, 25, 26, 27, 28 and 31.
Thanks for explanation. Agree that skipping weekend in the header would solve this issue. But is there any trick where weekends are included in the header and still the tasks are shown excluding weekends?
I think you can customize your Gantt chart, combining the formula IF that showed in the example with the formula WEEKDAY.
The formula WEEKDAY can identify Saturdays and Sundays, just combine that with IF and that should do the job.
Trust that helps.
incase the below didnt help, starting each function off as =workday will select only dates with weekdays
Outstanding! I created this right along with you in 10 minutes. This was so simple and easy.
This one is absolutely helpful. I learned and enjoyed your tutorial. Thank you so much. Keep it up!
Hi TECH. Thank you for the great simple guide, But I notice that e.g. the 4 days duration project ended up being 5 days.
As a fix, Cell E6 formula should be ' =D6+(C6-1) before the fill down. :)
And the project duration also increases by 1 right
Good solution for a simple Gantt chart.
How would you handle something more esoteric, where certain tasks may need to begin BEFORE one (or more) previous task(s)?
Also, how would you handle taking weekends out of the timeline, for such cases that require tasks to only be performed on weekdays?
Lastly (and this is a bit if a challenge), is it possible to create such a chart in Excel, which allows for some tasks that be performed only on weekdays, while other tasks can also be performed throughout the weekend? Can it also include tasks that can only be performed on the weekends AND span across multiple weekends?
I had the same question too, I developed it to take out the weekends but it doesn't work with me for the schedule
@@AhmedDaabis
It must be possible, but I really haven't had the time to devote to working towards a solution. Been swamped with other tasks. That's why I was hoping someone might post an answer. 🤗
This was easy to use! I was able to follow along and create my own chart. Thanks!
This is exactly what I was looking for!!! So easy to follow. Many thanks :-)
concerns regarding saturdays and sundays? what if there is now work on these days? how do you adjust? thanks
You need to use the workday function. Instead of +1 (at 8:26) you use =workday(G5,1). This will automatically calculate out the weekends. You also have the added benefit of figuring in holidays that need to be excluded. IF there is work on some of those days you may need add a cutout with an OR function or XOR.
@@Daeon108 Hi Daeion - this works - but then in the startdate / enddate how can i make the calculation - now it takes out the weekends, so i did a test with 7 working days, and since there are no weekends anymore it only shows 5 workingdays - you have maybe a solution for this too? Super gratefull for your help!
In 1st task (demolition), the task duration is 4, but in time line it fills 5 box (from Jun 1 to Jun 5). As one green cell shows 1 day, so as per that, there should be only 4 cells to be filled with green. So is there any changes? Or correction?
1. Maybe Weekend comes within.
2.After formula )-1 it works
Thank you so much for these videos. Big help!
Thank you so much for making things simple in a straight forward ways .
There are mistakes.. for example task id 1 demolition has task duration of 4 days.. the difference of July 5 and July 1 is 4 days too.. but if you count the cells its 5 green cells which means the duration is 5 days.
And the project duration calculation should be (end date - start date + 1)
No doubt the video is very helpful to me. Thanks. 😊
Thanks for highlighting. at 2:24 the formula must be "start date + task duration - 1" then the highlighted cells would be equal to duration.
Hello sir, love your chart, my issue is my chart is over a 8 month period and when I use the Min function it picks the lowest month value not the earliest date, ideas please.
No worries my mistake, it worked thanks!
Hello Shawn. It is good to hear you loved the chart. I see you've resolved the issue. If you ever want me to check out your spreadsheet, I'll be glad to do that if you email me at theexcelchallenge@gmail.com
Thanks for watching.
Shawn, I'm glad you resolved the issue. If it's worth for the community to know how you resolve it, I invite you to please share the solution, so others can get that knowledge too. Just a thought...
T-E-CH
Hello Shawn;
I'm struggling with the same initial problem you had, I see you've managed to win, please assist me champ.
Hello Marcus. Try this:
Refer back to the video at the 8:28 mark where I show a formula F5+1. In my example, +1 means next day.
For your project, do everything the same but instead of F5+1 type F5+7. In this case, +7 means next week.
Then simply fill the formula to the right the same way I do in the 8:35 mark.
And finally, enter your time duration in column C and your dates in column D.
This will resolve your issue and you will be able to present your 5-month project in a one-pager or in one-screen.
I trust this answers your question.
Thanks for watching and I hope you check out our other videos.
T-E-CH
Thank you so much for this. You were so easy to follow and I have actually managed to learn something! Excellent tutorial.
Hey! Like you were born with excel in mind already. Wow! I am learning, thanks...
how do you excluded weekends in this?
Hello Harsha. Thanks for watching. You will see there are several answers to that question already in the comments here. Check them out! One thing you can simply do is remove those columns with weekends and keep weekdays only. Try that. Stay tuned for more videos.
Hi..I'm a Project Manager and your explanation on creating a simple Gantt chart was helpful in getting me started. Now I need to know how to keep the chart functional when i have several tasks starting on the same day. The end date plus 1 is not working when I have multiple tasks starting on the same day. Can you help me?
Hello Rochelle. Project Management is a very broad topic and there is a lot of software on the market that can handle high complexity projects. If you do Project Management for a living, you may want to consider acquiring special software for that.
Now, if Excel does the job for your spreadsheets, all you have to do is to create your own rules. Use my video as a 'reference only' and change rules as you need to.
The end date plus 1 will not work, unless you point at different cells. All the customization is yours. I encourage you to try and error rules in your spreadsheet until you get the results you need.
Trust this advice helps.
T-E-CH
The Excel Challenge Thanks for your reply. I did purchase Microsoft Project 2016. Which is helpful with multiple project.
Very good Rochelle. I hope you do great in your job. Thanks for sharing this information with the community in case anyone else is on the same boat.
Keep coming back to check videos in the future.
T-E-CH
You're a hero. I struggle with anything tech but this is easy to understand
Thank you so much for this. It was so incredibly helpful!!!❤
Can you provide link in the description to download this file?
Hello Sravan,
Please send me an email to theexcelchallenge@gmail.com and I will send you the file. I don’t have a portal yet for viewers to download the files but that is something I have in mind for the future.
T-E-CH
How do I delete Saturday, Sundays, and Holidays from the construction schedule? I am just trying to create work days from Monday through Friday.
١٣؛
Use =workday() formula
Peter did you figure this out? I’m trying now myself
I used it before and viewed again since I forget anyway, its really really useful, Thanks so much
fantastic, just what I needed. BTW; I needed weeks instead of days, very easy to change; just used day+7 for the colums and added a row with WEEKNUMBER above it. Again, fantastic tutorial.
Hello, I am currently trying to make one in weeks as the project goes over 8 months, however when I follow his explanations the chart is difficult to read because of the many days. Can you help me on this, please?
how to exclude weekends from the timeline?
You need to use the workday function. Instead of +1 (at 8:26) you use =workday(G5,1). This will automatically calculate out the weekends. You also have the added benefit of figuring in holidays that need to be excluded.
That IF formula which is in f6 cell is not implementing .it shows error
Same for me, did you solve it?
Try ; instead of ,
Did you resolve the if problem? I am not able too
Your video was the Absolute Best! Thank you very much! :)
Logical error in calculating the project duration. The start and end dates need to be inclusive or you're gonna always be off by 1.
Actually that's cool, finishing 1day before deadline gives you enough time to reconcider and perfect your work or just Rest.
Sorry this doesn’t compute. For example the demolition task is 4 days but 5 days are highlighted on the timeline. Project duration is different to the days on task (even taking into account that you start next task on next day)
Hello Richard. I'm afraid you're right. I made a mistake in the 2:30 mark, where I should have entered a -1 in the formula. Using the same example you are using, if I am counting May 1 as day 1, and the task duration is 4 days, then the end date should be May 4. The formula should read =D6+C6-1 instead of =D6+C6
That's an easy fix. Sorry about the mistake. I hope you still were able to learn something new from the video.
Thanks for your comment.
T-E-CH
Haha, sat there for five minutes thinking wth, and of course, started to think is this weekends?, is it me?....thanks for posting ;)
Once I used the new formula (D6+C6-1). The formula at 5:17 doesnt work. Help
Also, your Project duration formula would change from =E2-D2 to =E2-D2+1...
Thank you so much for this invaluable video. It truly made my day.
That is amazing! Have never thought it might be so simple!
Every time I try this exact same formula, excel tells me my formula is incorrect?
so do I
dont use control F4 to lock the F5 ,D6E6, use the dollar sign instead
Formula to lock is
=If(AND(F$5>=$D6,F$5
OMG, EXACTLY WHAT I NEEDED -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- i got resources from 💜 FROE.BAR/RGGUYT 💜
Thank you, this helped me with my university assignment.
You nailed it man. Congratulations! and Thank you for making such a to the point video. Hats off to you. I am linking the Video i am even subscribing you. keep posting the good stuff.
Great tutorial, straight to the point!
Dry nice way to do this. Thank you!
I find the video quite useful. Just what i needed to solve an issue at work. Deeply appreciate it.
Man you"ve really saved me today. I had no option but to hit subscribe. Awesome, thank you.
Every step worked perfectly. Thank You!
I have been struggling to understand formulating gant charts but this is an excellent video. thank you.
Thank you so much for the simple and clear explanation. God bless you