For those of you who just want to copy and paste that first formula for the date thing: =IF(MONTH(D3-WEEKDAY((D3),2)+1) < MONTH(D3), (D3-28-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1, (D3-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1)
I have typed it in a dozen times and couldn't get it to work so was delighted to see this thank you. However have you noticed that it doesn't seem able to cope with a project date before the 28th January - it can't go back to proceeding year Even the early chart shows a project start date of the 1st but the first week commencing is the 3rd. I have spent too many hours trying to make it work 😂
Fantastic step by step! Some of the bits went a little quickly so a LOT! of pausing and repalying but I now have an amazing Gantt chart to use across multiple projects! well worth the time invested. One suggestion would be to post the different formulas in the description to make it easier and save a LOT of typing and re-typing when things go wrong.
Thank you, David, for an (almost) idiot-proof tutorial on creating a customized Gantt chart. It took me about 4 hours, but I'm thrilled with the result, and the fact that I can use this template for other projects in the future. Many, many thanks for your help!
@@12parkourdude12 I was about to comment this exact thing! I also can't get that formula to work, so if anyone has any advice, that would be really appreciated!
@@beckysings1669 Hi Becky, so it worked for me in the end. I basically thought I was being clever by putting the status table on another sheet (thought it would look neater) and I also had another sheet with something else on (so 3 sheets open, 1 including the gantt chart). Even though I selected 'this worksheet' on the conditional formatting, it wouldn't work. So, I decided to copy and paste the whole gantt chart (only) onto a brand new excel sheet, I quickly re-did the status table using the previous guide and kept it on the same sheet as the gantt chart this time. I then tried the 'golden colour for complete' formula and it worked. Not that technical of a fix, but it worked. I can't do the final 'percentage blocked' formula though. That still won't work. Good luck and keep singing!
Aside from the cut at 10:35ish, it was incredibly easy to follow. At that junction, there's really no explanation on what to actually do here, but fortunately I found the answer in the comments. Select K6 (assuming you followed the guide exactly) and then do ctrl+shift+end, and it will automatically select the entire chart, then you can go into the conditional formatting, select the create new rule option, paste the equation there, and then you can format the cell and continue on. Thank you David for such a helpful and insightful video!!
Lovely video, thank you! I followed along and made a couple of adjustments by using dynamic array formulas (so the formulas don't need to be dragged) for the dates and for the diamond shapes as follows: In K4: =SEQUENCE(, 52, IF(MONTH(D3-WEEKDAY((D3),2)+1) < MONTH(D3), (D3-28-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1, (D3-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1), 7) In K6: =IF(K4:BJ4=(F6:F23-WEEKDAY(F6:F23, 2)+1), "u", "") Just in case this is helpful to anyone 😊😊
I had lots of trouble adding the milestone diamonds. What I finally figured out is that the formula David uses, the "+1" portion of the formula means that it applies to week starting dates on Mondays. Mine was set up to start on Fridays simply because I created my sheet on a Friday. Monday=1 Tuesday=2 Wednesday=3 Thursday=4 Friday=5 I updated my formula to: =IF(H$3=($D6-WEEKDAY($D6,2)+5),"u","") and the diamonds appeared. Similarly, I could have changed my week starting date to a Monday and used the formula as written.
Wow. I thought I was good in Excel until I watched this. You're amazing! Yes, the video moves quickly, but lucky for me I'm already familiar with most of what you're presenting. You just tie it together beautifully. Congratulations and thank you!
I am a beginner and it took me about an hour. To be honest, I never thought I would make it, anyway I tried, and I actually made it. It wasn't that difficult because of the way you explained it all. Man, can´t thank you enough! blessings
@@davidmclachlanprojecthey David I'm making this right now, an absolute beginner here. I just don't get how to like get that calender thingy. I mean the rows and columns size and height after I how to do that?
Thank you, Mr. David, for sharing your valuable knowledge. You have inspired me to create my own BEST Gantt Chart. May God bless you. Keep produce videos like this!
This video is fantastic and the presenter does an amazing job. David's enthusiasm is infectious and I honestly had fun making a Gantt chart for my project - never thought I'd say that!
Thanks for the video David. I noticed the formulas change in K4 throughout the video. How are you changing the formulas without impacting how the date appears and the previous functionality? When I enter =K$4=(TODAY()-WEEKDAY(TODAY(),2)+1) in K4, the value changes to "FALSE".
Took me a bit longer than 15 minutes to get the hang of it. Wish I had read these comments beforehand and noticed somebody was kind enough to drop all the Excel formulae. Well worth the effort though. This is a great tool.
Excellent work. This tutorial was very easy to follow and easy to implement. As a recommendation you could add the formulas on the description for faster Gantt chart creation and that way we can focus more on the explanation.
Hello David, thank you for the tutorial, any chance we can switch this into a tracking sheet by days instead of weeks? What are the things we need to modify based on this tracking sheet? Thanks in advance!
@@davidmclachlanproject Hi David . Thanks for the video , was really helpful . How can I get this template please ? I tried creating it , but getting stuck at the end . Thanks 🙂
Thanks for the great video. One little hint for the other viewers: You have to take a close look at where he put the dollar signs ($). If you leave them out the cell dragging won't work how you want it to. I had to learn the hard way
I went brain dead during conditional formatting, I keep replaying, but the I don't get where the $ are on the cells, 4 hrs here now, I'm almost done...my God
Finally I made it, For problem on formula: Just manually add on excel, you can copy paste the formula if only on adding new rule on conditional formatting, if it's having error on adding new rule on conditional formatting you can paste the formula on excel sometimes it will prompt to correct the formula. you can also try to understand the formula somehow it might help like the $ sign I think it is use to apply the formula in all rows and columns. Also double check the formula if it's correct. Also important note follow the fancy thing he said right from the start when he is adding the date and month.
Hello, thank you for showing us how to do it. would it be possible to copy the formulas into the description so we can copy and past them in and change the cell references. I have tried to type up the formula into the cells and I keep getting error messages. It says it is too long to even try the first one. Thank you
Thanks for such wonderful video, but I failed to do the today’s bar. Tried few times with the possibilities on my understanding. Can u teach again on where should I type the formula? Thanks
Thank you David, this is incredibly useful as I am a civil engineering student at a university that does not provide access to MS Project regardless of the crazy fees that I'm paying 🤣
awesome job - one additional need would be a "dependencies" function where certain tasks can be linked and thus influence each others timeline back and forth. Other then that fantastic tutorial and product
Stuck on the step around 9:40 - making it show a u when that date falls within the week selected above. the formula isn't showing any errors and appears to be pulling from the right places but I am not getting the u on the week it should appear (the end date is 4/30/2022 and it should appear in the work week of 25-Apr). I even tried changing the end date to match the week day exactly and it doesn't seem to want to work. Anyone have any tips / advice?
I'm currently undergoing a Site Management course. I've been banging my head against the wall trying all sorts of formulas to get this right. This is by-far one of the best examples of a self-build Gantt Chart, and has become indispensable to me! Many Thanks, David! P.S. I've managed to manipulate a formula to introduce and over run on any particular task! Again Thanks David!
Well done. Though it is missing a key component of a gantt chart: Dependencies. The whole purpose of a (complete) gantt chart is to show what tasks depend on what other tasks and what the impact of changing one or more tasks has on the schedule. If your intention was artistic in nature, this is very good. But don't sell this as a gantt chart. Thanks for showing how to build our own sheets. Much more impactful than simply uploading someone else's sheets.
I agree. The point of using a tool like MS Project is for predecessors and successors and the whole scheduling engine. Also, summary tasks and other features for navigating a large project schedule. Not to mention resourcing features. Excel might be OK for a very simple task list but I can't imagine running an actual project without something like MS Project!
I think how important this is depends on the size of the project, Many mini projects can and are managed on excel sheets manually and this automates some of the basic functions of laying out a chart. I find managing an ms project plan cumbersome in pretty much all but the most rigidly defined projects. Equally i often find faults in the ms-project scheduling engine that can put your entire plan out of kilter, meaning you up end spending more time getting your plan to behave correctly than you do actually delivering anything. In the kind of environments i manage manage projects in i need something that is quickly editable on the fly at a whim, without being to concerned with what impact changing one line may have on the rest of the plan. I can make notes against the cells in excel and cater for the dependencies later when i have time. Also in complex changing environments dependencies are always changing , and often things that you thought were dependencies turn out not to be and vica versa. In my experience having a plan that is a little looser in is restrictiveness can actually be helpful for a busy project manager.
@@danielbarnatt9332this has been my experience as well. Any MS project schedule I create is like a beautiful work of art on Day 1. But I’ve never successfully tracked progress in it. I’ve been developing a “task list” in excel that simply has tasks, estimated hours per task, and a “percent complete” cell, which drives 10 conditionally formatted cells that represent a “gas gauge” - how close to done the task is, From 1-10 Real life is always way more complicated than an MS project schedule. The fact that project doesn’t let me take notes anywhere on the screen just kills me. I’m reinvigorated with the idea of doing this in excel (and keeping it very simple) due to recently working with a very competent project manager who uses a white board and post-it notes. He doesn’t use a computer at all, let alone MS project. (And I just save the excel file with a new date in the filename each time, so I could always go back in time super easily if I ever wanted to…)
Thank you so much! As someone who struggles with even the basics of Excel, I'm amazed that I was able to create such a professional and functional Gantt chart following your instruction! All of the formulas worked for me bar the formula for creating the days for the calendar and the one for creating the golden colour when a task is complete; if anyone had any ideas about why they didn't work and how to fix them, I'd really appreciate that :)
Thanks, David for a great video and tutorial from someone who has never used a macro in his life, I found it really really helpful (although I had to run it a 0.25 speed a couple of times). If you just show me how to use week numbers instead of days/weeks/months, and also to show how to schedule pre-start activities - that would be fantastic!! Thanks again!
Hello and thank you for this video. I am having an issue with trying to get the Gantt bars, the formula =K$4=(TODAY()-WEEKDAY(TODAY(),2)+1) comes back as FALSE. And I am also confused about whether I'm replacing the formula for the weeks in K4 with this one.
The Gantt bar formulae need to be entered as Conditional Formatting rules. If you've created all the dates in row 4, and all the row data you are using in columns C to I, then you need to click on cell K6, then press Ctrl+Shift+End and it'll highlight the range of cells you are testing. Up on the Home menu, click Conditional Formatting, New Rule... then select the bottom rule type "Use a formula to determine which cells to format", enter the formula in the box below, set the Format... you want. Click OK, then Apply, then OK.
@David McLachlan if you add this code into it =IF(ISBLANK($B6),TRUE, FALSE) into a rule with the right fills and what not it can hide cells, not in use until task number is add to make it look even cleaner
Mr. David .. first of all thanks for your efforts and wish you all the best, some functions are not working with me like milestone (the diamond) and blokes filling .. any idea? Is it related to the window or something else? Can you please help in this?
A couple of thoughts: drop down lists maybe on a separate 'tools' sheet: easier to use across tabs and to update. Use a table for the list. % complete only for when the output is countable. Estimates never work. If there is no countable output, the activity is 0% until it is 100%. And 100% means accepted by the PM (and the client). IMO, better to have an expected completion date, so it is concrete and drives commitment on the reporter.
@@davidmclachlanproject David, glad you found my thoughts of interest. One more thought, in my domain, a milestone is a durationless event; I identify a milestone as an activity of zero duration: so start and end dates are the same. They usually come at the end of a sequence of activities that produce a work package or discrete component.
Is it too much to ask if you could kindly send your template? As you can imagine someone needing an advanced chart like this is in crunch time mode 😢. Thank you so much in advance 🙏🏻
Nice work. I added the following formula and named range to the ID# so that deleting a task renumbers and keeps the tasks id in order =IF(ROW(id)=ROW($B$5)+2,1,MAX(prev_col_range)+1) named rage id = $B5 and named range prev_col_range = $B$5:B4
I could not get this going in B6 or 7 or 8 etc, even after correcting the rage typo to range. @paulkelly6446 any tips where to insert this please? I think its a great idea.
wonderful video. I so like that this is very fast but very clear and straight to the point as well. really really cool... thanks so much for sharing this.
This is a great video but for us beginners it goes a little fast. I wish it was a little more broken down in the formatting portions and adding the tasks and stuff. Overall still a great video.
In my opinion, it is the best timeline chart I have ever seen. I need a help on the "Percent Done". The formula of the Percent Done on the Bar does not work for me: =AND($I6>0, K$4 = $E6 - WEEKDAY($E6, 2)+1). Shows an error. Help me!
had the same but resolved it this way: Added a closing parenthesis for the WEEKDAY function before the +1 in the second argument. Changed the greater than or equal to sign (>=) to just the greater than sign (>) in the third argument.
I keep returning to this video even though I have followed along and recreated the template myself as a learning exercise. I have created a daily version for a more detailed view but use the weekly version to provide an overview. Simply superb! Thank you again. 👍😃
Hi there! I'm looking at a daily version as well, but for some reason the =IF(K$4=($F6-WEEKDAY($F6,2)+1),"u","") formula doesn't work in the daily version. It works for 3 rows but the rest are a few days out. Did you use a different formula to place the diamonds on the gantt? Thanks!
I am trying to set one for daily tracking as well but am having issues getting the conditional formatting formulas figured out. Any way you can provide those?
Absolutely amazing and much needed, I was looking for some advanced Gantt chart for. months, and here I got it, many thanks, considering buying your template, looking for some discount !
May I suggest that we organize (organise) a project to incorporate some / all of the desired features: • Critical Path Analysis • Dependencies of Activities • Resources Loading • "What if" scenario calculations • Resource leveling / smoothing • Unexpected loss / delay of a resource • •
@@nicholastugume1081 same, my ghantt bar isnt visible even after applying the right formula. Some formatting in the later part is not working too. Can someone help - what may be going wrong ?
Wow, this is great and your comments serve as an FAQ. You've made some difficult conditional formatting and rules easy. I'm nearly complete, however I couldn't figure out how to do the rule regarding the gold shading when the data validation is marked complete. I believe I copied the formula correct and have the proper cells selected for the rule. Any ideas or workarounds, or I'm happy to send you what I've built. Thank you again - great teaching!
Hi, I had this and was stumped for ages. I thought I was being clever and putting the 'status table' on a new sheet and also had another sheet with something else on. Even though I selected the 'this worksheet' option on the conditional formatting, it didn't work. I randomly tried copying and pasting the entire created gantt chart (to that point) into a completely brand new worksheet on excel and quickly re-created the 'status table' and placed it at the bottom of the chart. When I highlighted the gannt chart area and entered the conditional formatting, it decided to work. Though I am now stumped on the last bit about the blocked bars being a different colour, can't get that to work.
Having taken courses on LinkedIn Learning and other TH-cam channels and reading books on MS Excel, I can say this lesson of yours is one of the best, I've taken on Excel so far! Thanks a lot Buddy, Keep it rolling....
This is really good! Can you show how in this example -- John is ready for another task 16 May. How do you add that without losing the previous tracked task? That way, you could forward allocate tasks and the entire year would show a record of time allocation by team member. When the director asks "what have your people been working on all year?".
Hi , great and perfect work.. Please, so I merge and Center the calendar to have 4 weeks and 5 weeks? It kept saying “can’t merge and centre cells of different sizes”
I was following along fine and then once I got to the milestone integration and with the formulas I got lost. I have tried placing in the exact formula to no avail so I can't go any further with the GANNT creation. :-(
One thought: if you're not using the "fancy" start date formula, make sure your project date starts on a Monday. This should work with the rest of the formulas!
@@davidmclachlanproject I tried changing the start date to Monday and it still doesn’t work. It’s a shame I really like to look at this chart but I can’t go any further. I spent a few hours designing this and now I can’t use it
Excellent clip, thank you David! Very detailed and helpful. One question: is there a way to edit/ament the chart and have the months broken into weeks and the weeks into dates, and then by using the "Group" function under Date to collapse or expand the cells?
I can honestly say, my team was impressed when I whipped this out to present the other day
For those of you who just want to copy and paste that first formula for the date thing:
=IF(MONTH(D3-WEEKDAY((D3),2)+1) < MONTH(D3), (D3-28-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1, (D3-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1)
Thank you 😢
you're a hero Michelle
What kind of kindness do have man, thanks a million!
I have typed it in a dozen times and couldn't get it to work so was delighted to see this thank you. However have you noticed that it doesn't seem able to cope with a project date before the 28th January - it can't go back to proceeding year Even the early chart shows a project start date of the 1st but the first week commencing is the 3rd. I have spent too many hours trying to make it work 😂
Please share formula for the daywise calander, Milestone Diamond, Adding the Gantt Bar, Percentage Complete, Done Golden Color, Showing blocker items
David, you're a natural born teacher. Very much appreciate your positive and encouraging tutorial on how to do these things for yourself.
Please never stop making videos. We need more teachers like you.
Fantastic step by step! Some of the bits went a little quickly so a LOT! of pausing and repalying but I now have an amazing Gantt chart to use across multiple projects! well worth the time invested.
One suggestion would be to post the different formulas in the description to make it easier and save a LOT of typing and re-typing when things go wrong.
Thank you, David, for an (almost) idiot-proof tutorial on creating a customized Gantt chart. It took me about 4 hours, but I'm thrilled with the result, and the fact that I can use this template for other projects in the future. Many, many thanks for your help!
May I ask how you went about adding the 'golden colour to show complete' formula please? It won't work for me.
@@12parkourdude12 I was about to comment this exact thing! I also can't get that formula to work, so if anyone has any advice, that would be really appreciated!
@@beckysings1669 Hi Becky, so it worked for me in the end. I basically thought I was being clever by putting the status table on another sheet (thought it would look neater) and I also had another sheet with something else on (so 3 sheets open, 1 including the gantt chart). Even though I selected 'this worksheet' on the conditional formatting, it wouldn't work. So, I decided to copy and paste the whole gantt chart (only) onto a brand new excel sheet, I quickly re-did the status table using the previous guide and kept it on the same sheet as the gantt chart this time. I then tried the 'golden colour for complete' formula and it worked. Not that technical of a fix, but it worked. I can't do the final 'percentage blocked' formula though. That still won't work. Good luck and keep singing!
Aside from the cut at 10:35ish, it was incredibly easy to follow. At that junction, there's really no explanation on what to actually do here, but fortunately I found the answer in the comments. Select K6 (assuming you followed the guide exactly) and then do ctrl+shift+end, and it will automatically select the entire chart, then you can go into the conditional formatting, select the create new rule option, paste the equation there, and then you can format the cell and continue on.
Thank you David for such a helpful and insightful video!!
THANK YOU
Lovely video, thank you!
I followed along and made a couple of adjustments by using dynamic array formulas (so the formulas don't need to be dragged) for the dates and for the diamond shapes as follows:
In K4: =SEQUENCE(, 52, IF(MONTH(D3-WEEKDAY((D3),2)+1) < MONTH(D3), (D3-28-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1, (D3-DAY(D3)+7) - WEEKDAY((D3-DAY(D3)+7),2)+1), 7)
In K6: =IF(K4:BJ4=(F6:F23-WEEKDAY(F6:F23, 2)+1), "u", "")
Just in case this is helpful to anyone 😊😊
I had lots of trouble adding the milestone diamonds. What I finally figured out is that the formula David uses, the "+1" portion of the formula means that it applies to week starting dates on Mondays. Mine was set up to start on Fridays simply because I created my sheet on a Friday.
Monday=1
Tuesday=2
Wednesday=3
Thursday=4
Friday=5
I updated my formula to: =IF(H$3=($D6-WEEKDAY($D6,2)+5),"u","") and the diamonds appeared. Similarly, I could have changed my week starting date to a Monday and used the formula as written.
you are very right..l had to check the 10 Jan on the 2024 calendar and its a Wednesday, which is 3..so l added 3..the diamonds appeared..
Dude, thank you so much for this. I was like wtf dd i do wrong lolllll
Wow. I thought I was good in Excel until I watched this. You're amazing! Yes, the video moves quickly, but lucky for me I'm already familiar with most of what you're presenting. You just tie it together beautifully. Congratulations and thank you!
Took me 4 hours...lol, but that was great. Thanks!!!
It took me 2 weeks, so you did well 😂
Your positivity has made excel bearable for me. Thank you
I am a beginner and it took me about an hour. To be honest, I never thought I would make it, anyway I tried, and I actually made it.
It wasn't that difficult because of the way you explained it all. Man, can´t thank you enough! blessings
An hour, that's impressive! It took me about a week after work to figure out all the bits and pieces, so: time saved 🙂
@@davidmclachlanproject can you please give us the functions for day per day ?
@@davidmclachlanprojecthey David I'm making this right now, an absolute beginner here. I just don't get how to like get that calender thingy. I mean the rows and columns size and height after I how to do that?
@@yourfatherofyourmother2035 Did you ever find out
Thank you, Mr. David, for sharing your valuable knowledge. You have inspired me to create my own BEST Gantt Chart. May God bless you. Keep produce videos like this!
This video is fantastic and the presenter does an amazing job. David's enthusiasm is infectious and I honestly had fun making a Gantt chart for my project - never thought I'd say that!
Hey, I just started my first project coordinator job and replicated your spreadsheet. This is epic. Thanks.
Thanks for the video David. I noticed the formulas change in K4 throughout the video. How are you changing the formulas without impacting how the date appears and the previous functionality? When I enter =K$4=(TODAY()-WEEKDAY(TODAY(),2)+1) in K4, the value changes to "FALSE".
Have never put together a Gantt Chart or done any formal training and this video has saved me! Thank you David!
Hands down the most straight forward and visually appearing GANTT chart
Took me a bit longer than 15 minutes to get the hang of it. Wish I had read these comments beforehand and noticed somebody was kind enough to drop all the Excel formulae.
Well worth the effort though. This is a great tool.
For the Milestones, none of my conditional formatting is working when plugging in the formulas, and the wingdings aren't showing up. Need help!
I think you have select all the cells where you want the wingdings first.
One of the best Excel tutorials I've ever done! All the formulas worked and you made it easy to understand. Thanks so much!
Excellent work. This tutorial was very easy to follow and easy to implement. As a recommendation you could add the formulas on the description for faster Gantt chart creation and that way we can focus more on the explanation.
my thought exactly
I can't get any of the formulas to work, giving up. Wasted soooo much time trying. Will just buy a project software.
You are way to inthousiast about pastel colors!! haha, but thank you very much you make turorials actually fun to watch. Thanks for those tips!
Hello David, thank you for the tutorial, any chance we can switch this into a tracking sheet by days instead of weeks? What are the things we need to modify based on this tracking sheet? Thanks in advance!
Thank you so much for a great explanation for a simple but yet very useful method.
Hi! I passed my PMP exam with above target in all three categories. Thanks for your videos!
That is amazing Linda, congratulations! You have conquered a very difficult task 🙂
@@davidmclachlanproject Hi David . Thanks for the video , was really helpful . How can I get this template please ? I tried creating it , but getting stuck at the end . Thanks 🙂
You have literally SAVED MY LIFE today with this!!!! Thank you!!!!!!!
Thanks for the great video. One little hint for the other viewers: You have to take a close look at where he put the dollar signs ($). If you leave them out the cell dragging won't work how you want it to. I had to learn the hard way
I went brain dead during conditional formatting, I keep replaying, but the I don't get where the $ are on the cells, 4 hrs here now, I'm almost done...my God
Finally I made it,
For problem on formula:
Just manually add on excel, you can copy paste the formula if only on adding new rule on conditional formatting, if it's having error on adding new rule on conditional formatting you can paste the formula on excel sometimes it will prompt to correct the formula. you can also try to understand the formula somehow it might help like the $ sign I think it is use to apply the formula in all rows and columns. Also double check the formula if it's correct. Also important note follow the fancy thing he said right from the start when he is adding the date and month.
Hi @feeldulfo9387, could you please send me the completed Gantt chart? I'm having trouble with the formulas.
Thanks David, this is one of the best tutorials available for Gantt Chart creation and Conditional Formatting
Hi David, The level of Dedication and hard work you are putting in to make such a mind-blowing content, you deserve millions of Subscribers.
This is a straight-up amazing walkthrough on how to create a well functioning Gantt Chart. Thanks so much for sharing, it will be put to great use!
Great experience to create successfully the template with zero knowledge - Thank You David
David, can dependencies be done as well that becomes visible as dashboard in XL?
Hello, thank you for showing us how to do it. would it be possible to copy the formulas into the description so we can copy and past them in and change the cell references. I have tried to type up the formula into the cells and I keep getting error messages. It says it is too long to even try the first one. Thank you
amazing, thanks! Very exciting, clear, well-presented video I must say!
Simply amazing! I had no idea excel sheets could look so aesthetic. You've completely changed my life, this Gantt chart is a game-changer!
@@dwightPhoenix How and where?
Thank you so much for this!!
I've created a Gantt chart that my boss will be happy with for a company-wide project!
⭐⭐⭐⭐⭐
Thanks for such wonderful video, but I failed to do the today’s bar. Tried few times with the possibilities on my understanding. Can u teach again on where should I type the formula? Thanks
Same place i got stuck because if I put the formular it gives me error. Did you find a way around it?
@@ibd991 yes. I found the solution. how about you?
@@chloehugaga2203can you please share it?
I like this video ❤❤❤❤❤.
Thank you David, this is incredibly useful as I am a civil engineering student at a university that does not provide access to MS Project regardless of the crazy fees that I'm paying 🤣
Thank you for the detailed explanation. Really helped me out when my boss asked me to prepare a project tracker...
awesome job - one additional need would be a "dependencies" function where certain tasks can be linked and thus influence each others timeline back and forth. Other then that fantastic tutorial and product
I would like to give 1000 thumbs up for this, but unfortunately that is not possible 🙂, Thanks David.
Stuck on the step around 9:40 - making it show a u when that date falls within the week selected above. the formula isn't showing any errors and appears to be pulling from the right places but I am not getting the u on the week it should appear (the end date is 4/30/2022 and it should appear in the work week of 25-Apr). I even tried changing the end date to match the week day exactly and it doesn't seem to want to work. Anyone have any tips / advice?
Hey, did you get an answer or resolve yourself? I also can’t get the symbol to appear.
manually add the formula, also do the fancy thing formula he said right at the beginning when he just start adding the day and month e.g., 22-Sep
Work Around: I inserted a Column for Week Number for the End Date and used this formula =IF($G5-WEEKNUM(L$4)=0,"u"," ")
@@settychey, how do you define the week number in the column?
I'm currently undergoing a Site Management course. I've been banging my head against the wall trying all sorts of formulas to get this right. This is by-far one of the best examples of a self-build Gantt Chart, and has become indispensable to me! Many Thanks, David!
P.S. I've managed to manipulate a formula to introduce and over run on any particular task! Again Thanks David!
Well done. Though it is missing a key component of a gantt chart: Dependencies. The whole purpose of a (complete) gantt chart is to show what tasks depend on what other tasks and what the impact of changing one or more tasks has on the schedule. If your intention was artistic in nature, this is very good. But don't sell this as a gantt chart.
Thanks for showing how to build our own sheets. Much more impactful than simply uploading someone else's sheets.
I agree. The point of using a tool like MS Project is for predecessors and successors and the whole scheduling engine. Also, summary tasks and other features for navigating a large project schedule. Not to mention resourcing features. Excel might be OK for a very simple task list but I can't imagine running an actual project without something like MS Project!
See my question about the FOUR types of dependencies.
I think how important this is depends on the size of the project, Many mini projects can and are managed on excel sheets manually and this automates some of the basic functions of laying out a chart. I find managing an ms project plan cumbersome in pretty much all but the most rigidly defined projects. Equally i often find faults in the ms-project scheduling engine that can put your entire plan out of kilter, meaning you up end spending more time getting your plan to behave correctly than you do actually delivering anything. In the kind of environments i manage manage projects in i need something that is quickly editable on the fly at a whim, without being to concerned with what impact changing one line may have on the rest of the plan. I can make notes against the cells in excel and cater for the dependencies later when i have time. Also in complex changing environments dependencies are always changing , and often things that you thought were dependencies turn out not to be and vica versa. In my experience having a plan that is a little looser in is restrictiveness can actually be helpful for a busy project manager.
@@danielbarnatt9332this has been my experience as well.
Any MS project schedule I create is like a beautiful work of art on Day 1. But I’ve never successfully tracked progress in it.
I’ve been developing a “task list” in excel that simply has tasks, estimated hours per task, and a “percent complete” cell, which drives 10 conditionally formatted cells that represent a “gas gauge” - how close to done the task is,
From 1-10
Real life is always way more complicated than an MS project schedule.
The fact that project doesn’t let me take notes anywhere on the screen just kills me.
I’m reinvigorated with the idea of doing this in excel (and keeping it very simple) due to recently working with a very competent project manager who uses a white board and post-it notes. He doesn’t use a computer at all, let alone MS project.
(And I just save the excel file with a new date in the filename each time, so I could always go back in time super easily if I ever wanted to…)
Just created a Gantt following this video. Turned out great. Can't wait to use this for my team's project!
You are my life saver THANK YOU SO MUCH for the amazing content!!!!!
p/s my team are IMPRESSED with this ganttchart for real !!!!!
Thank you so much! As someone who struggles with even the basics of Excel, I'm amazed that I was able to create such a professional and functional Gantt chart following your instruction! All of the formulas worked for me bar the formula for creating the days for the calendar and the one for creating the golden colour when a task is complete; if anyone had any ideas about why they didn't work and how to fix them, I'd really appreciate that :)
This is considerably better than the ones in my work. Thank you and your formulas actually work, unlike 90% of the ones on TH-cam.
Mine are not working though. "problem with formula"
@@PatchedBanditI need to change “,” to “;” and worked for me. Hope you solve that problem.
Thanks, David for a great video and tutorial from someone who has never used a macro in his life, I found it really really helpful (although I had to run it a 0.25 speed a couple of times). If you just show me how to use week numbers instead of days/weeks/months, and also to show how to schedule pre-start activities - that would be fantastic!! Thanks again!
Hello and thank you for this video. I am having an issue with trying to get the Gantt bars, the formula =K$4=(TODAY()-WEEKDAY(TODAY(),2)+1) comes back as FALSE. And I am also confused about whether I'm replacing the formula for the weeks in K4 with this one.
Great question, having the same issues.
Great question. Having the same issue.
The Gantt bar formulae need to be entered as Conditional Formatting rules. If you've created all the dates in row 4, and all the row data you are using in columns C to I, then you need to click on cell K6, then press Ctrl+Shift+End and it'll highlight the range of cells you are testing.
Up on the Home menu, click Conditional Formatting, New Rule... then select the bottom rule type "Use a formula to determine which cells to format", enter the formula in the box below, set the Format... you want. Click OK, then Apply, then OK.
Great video, yes I am having the same issue - please help
You are a Master at this. ... and it was also a good presentation. God bless you!
@David McLachlan if you add this code into it =IF(ISBLANK($B6),TRUE, FALSE) into a rule with the right fills and what not it can hide cells, not in use until task number is add to make it look even cleaner
👍
💀☠💀masterpiece comment. what a genius😏
bye
@@kelon_ good. stay away
Nice presentation
Mr. David .. first of all thanks for your efforts and wish you all the best, some functions are not working with me like milestone (the diamond) and blokes filling .. any idea? Is it related to the window or something else? Can you please help in this?
This is slick - thanks so much! Love your energy into the video. Going to use this for my next project.....people may think I know what I'm doing! :)
😄😄😄💪
OMG! i Canno believe this but I made the GANTT CHART and its amazing! Thank you for this tutorial.
great video; may I ask if you provide the formula where you use to format the dates across the year?
I think you are asking about 2 consecutive calendar years:
How do you sort by start dates if your project spands TWO years?
Such contageous enthusiasm. I love how a square becomes "a beautiful diamond". Thanks a lot for making this one.
A couple of thoughts: drop down lists maybe on a separate 'tools' sheet: easier to use across tabs and to update. Use a table for the list. % complete only for when the output is countable. Estimates never work. If there is no countable output, the activity is 0% until it is 100%. And 100% means accepted by the PM (and the client). IMO, better to have an expected completion date, so it is concrete and drives commitment on the reporter.
These are great ideas, certainly do-able 🙂
@@davidmclachlanproject David, glad you found my thoughts of interest. One more thought, in my domain, a milestone is a durationless event; I identify a milestone as an activity of zero duration: so start and end dates are the same. They usually come at the end of a sequence of activities that produce a work package or discrete component.
Amazing!! thanks a lot for this super great gant chart!! very useful and super well explained !! the best video pof Gantt chart ever watched
Great job. I completed the Gantt chart and am very excited to implement it. You are excellent, sir!
Is it too much to ask if you could kindly send your template? As you can imagine someone needing an advanced chart like this is in crunch time mode 😢. Thank you so much in advance 🙏🏻
This is really fantastic. Thanks so much, what a great tool and looks super slick. Cheers Mate from the US!
David, you're making me look like a genius. This has been so informative and useful. Thanks so much for sharing.
Well done mam you are doing great 👍😃 from India
Hi David, great Gannt chart and very easy to follow and develop, just wondering is there an easy way to make the date dependant?
Thanks fella. I think you're brilliant...a bit too quick for me...but that's what pause is for.
Very very impressive chart. Im 100% going go make one, its 100% going take me hours to do though 🤣
It took me two weeks to figure it all out and put it together, but I am not the fastest person 😂
Im a project manager and that chart is going to become part of my arsenal. Thank you!
Thank you very much for the tutorial! My head is already aching from working on this Gantt chart. My boss has been requesting it for weeks.
Nice work. I added the following formula and named range to the ID# so that deleting a task renumbers and keeps the tasks id in order =IF(ROW(id)=ROW($B$5)+2,1,MAX(prev_col_range)+1) named rage id = $B5 and named range prev_col_range = $B$5:B4
Nice one! 👍
How to change if i want month
Hey Paul what cells do you apply this to?
I could not get this going in B6 or 7 or 8 etc, even after correcting the rage typo to range. @paulkelly6446 any tips where to insert this please? I think its a great idea.
wonderful video. I so like that this is very fast but very clear and straight to the point as well. really really cool... thanks so much for sharing this.
This is a great video but for us beginners it goes a little fast. I wish it was a little more broken down in the formatting portions and adding the tasks and stuff. Overall still a great video.
Agree. Great video but for a beginner, formatting the cells went way too quickly.
You tube allows for playback down to 25% to 75% of normal.
Legend, super professional. Thank you so much!
In my opinion, it is the best timeline chart I have ever seen. I need a help on the "Percent Done". The formula of the Percent Done on the Bar does not work for me: =AND($I6>0, K$4 = $E6 - WEEKDAY($E6, 2)+1). Shows an error. Help me!
I'm getting the same :(
same
He never responded did he?
I'm stuck on the same thing.
I think that's where almost everyone got stuck...is he going to respond on it or not?
had the same but resolved it this way:
Added a closing parenthesis for the WEEKDAY function before the +1 in the second argument.
Changed the greater than or equal to sign (>=) to just the greater than sign (>) in the third argument.
I keep returning to this video even though I have followed along and recreated the template myself as a learning exercise. I have created a daily version for a more detailed view but use the weekly version to provide an overview. Simply superb! Thank you again. 👍😃
=IF(K$4=($F6-WEEKDAY($F6,2)+1),"u","")
this one didn't work for me
how to fix that
Hi there! I'm looking at a daily version as well, but for some reason the =IF(K$4=($F6-WEEKDAY($F6,2)+1),"u","") formula doesn't work in the daily version. It works for 3 rows but the rest are a few days out. Did you use a different formula to place the diamonds on the gantt? Thanks!
@@summajae intriguing, i have come across the exact same issues. maybe its a version of excel thing??? mine do not work at all
I am trying to set one for daily tracking as well but am having issues getting the conditional formatting formulas figured out. Any way you can provide those?
Absolutely amazing and much needed, I was looking for some advanced Gantt chart for. months, and here I got it, many thanks, considering buying your template, looking for some discount !
Finally got the hang of it, many many thanks, super worth having spent my 5 hours with you...All the very best and hope for more videos to come :)
May I suggest that we organize (organise) a project to incorporate some / all of the desired features:
• Critical Path Analysis
• Dependencies of Activities
• Resources Loading
• "What if" scenario calculations
• Resource leveling / smoothing
• Unexpected loss / delay of a resource
•
•
Awesome video.
**for the “wingding formula” to work you must put the long formula in the calendar section and not the simple method**
Hi David, hope you are open for development suggestions, if so @1:22 when you select complete, the progress should ideally default to 100%.
I agree…what formula would you recommend?
Really good tutorial, concise, useful and full of tricks! thanks!
Excellent tutorial! Exactly what I was looking for. Thank you for taking the time to create and share it.
I am stranded at the addition of ghantt bars. what could be doing wrong
@@nicholastugume1081 i managed to do that, but i am stucked at the diamond milestone
@@nicholastugume1081 same, my ghantt bar isnt visible even after applying the right formula. Some formatting in the later part is not working too. Can someone help - what may be going wrong ?
Thanks David. Watched Gantt chart clip and was a great help. Thx. Andrew, New subscriber
Do you have a tutorial on how to add dependencies between tasks? Great video!
Not yet but it's a good idea 🙂 Will get on it soon!
@@davidmclachlanproject loved this video and was able to get a gantt up and running, Having the ability to do dependencies would be amazing
Wow, this is great and your comments serve as an FAQ. You've made some difficult conditional formatting and rules easy. I'm nearly complete, however I couldn't figure out how to do the rule regarding the gold shading when the data validation is marked complete. I believe I copied the formula correct and have the proper cells selected for the rule. Any ideas or workarounds, or I'm happy to send you what I've built. Thank you again - great teaching!
Hi, I had this and was stumped for ages. I thought I was being clever and putting the 'status table' on a new sheet and also had another sheet with something else on. Even though I selected the 'this worksheet' option on the conditional formatting, it didn't work. I randomly tried copying and pasting the entire created gantt chart (to that point) into a completely brand new worksheet on excel and quickly re-created the 'status table' and placed it at the bottom of the chart. When I highlighted the gannt chart area and entered the conditional formatting, it decided to work. Though I am now stumped on the last bit about the blocked bars being a different colour, can't get that to work.
Hi @David,
Great work. How can we break down the timeline from weeks to days. Your inputs will be really helpful. Thank you for the video. 🙏
@David, answer please to this question
Do we already have an answer for this? 🙏
Instead of +7 change to +1
@@vcanadian1971 The conditional formatting formulas need to be changed as well.
@@jimmietraylor7413 how can we remove the Conditional formatting on the others b/c it is throwing + 2 when I have days and not weeks
Having taken courses on LinkedIn Learning and other TH-cam channels and reading books on MS Excel, I can say this lesson of yours is one of the best, I've taken on Excel so far!
Thanks a lot Buddy, Keep it rolling....
This is really good! Can you show how in this example -- John is ready for another task 16 May. How do you add that without losing the previous tracked task? That way, you could forward allocate tasks and the entire year would show a record of time allocation by team member. When the director asks "what have your people been working on all year?".
It is the same example I requested now hope David give us a tutorial or a template to buy with that issue. I really need it for planning tasks!!!
Hi , great and perfect work..
Please, so I merge and Center the calendar to have 4 weeks and 5 weeks? It kept saying “can’t merge and centre cells of different sizes”
How can I download this
Work smarter not harder 😂😂😂
Clearly you are an Excel time genius! Thanks!
Its crazy how you manage to use excel to create such a complex and useful gantt chart. I can barely get my formatting to work hahaha. Great stuff.
A2¹
SUPER HELPFUL!! One thing, as a new learner, I did not realize that the formulas you are plugging into the formatting don't exist in any cells yet.
I was following along fine and then once I got to the milestone integration and with the formulas I got lost. I have tried placing in the exact formula to no avail so I can't go any further with the GANNT creation. :-(
One thought: if you're not using the "fancy" start date formula, make sure your project date starts on a Monday. This should work with the rest of the formulas!
@@davidmclachlanproject I tried changing the start date to Monday and it still doesn’t work. It’s a shame I really like to look at this chart but I can’t go any further. I spent a few hours designing this and now I can’t use it
@@davidmclachlanproject I tried this on a Monday and it works! Thank you
Thank you so very much! I feel so proud of myself...courtesy of your shared wisdom and insane Excel skills!
You should explain the formulas as well in depth.. it made it very fast.. super difficult to learn for a beginner
What a Gantt chart! Really the best! Thank you so much, David.
Excellent clip, thank you David! Very detailed and helpful. One question: is there a way to edit/ament the chart and have the months broken into weeks and the weeks into dates, and then by using the "Group" function under Date to collapse or expand the cells?
Super helpful, thanks for taking the time to do this and share it with all of us. !