Thank you, I enjoyed this. Can you tell me more about, or share another video on how the check the status checkbox might affect the sheet and timeline? Also, how do you use and configure the Progress and Progress % columns?
Thanks for your questions reference the Automatic Daily Gantt Chart: bit.ly/automatic-gantt-chart You can mark tasks as completed with the status checkbox which will grey out the row but this will still be displayed in the Gantt Chart. It's possible to hide completed tasks (rows) by using the filter in cell A4 to filter out TRUE values in column A, so that you will only see outstanding tasks in the table and in the Gantt chart. In terms of the Progress and Progress % columns (I and J), you can manually add a percentage completed in column J and this will visually represent as a bar chart in column I for the chosen colour in the label dropdown in column B. The Gantt chart in the Project Management template is slightly different as this updates the progress bar automatically based on the the number of net working days remaining for a given task. bit.ly/project-management-template I hope that helps 😉
Very helpful thank you but I have a question: why when I change the starting the date , the dates on the timeline change also? is there any way to keep it fix and the timeline do not change every time when I change the stating date?
Thanks for your question @PamelaYammine. If you are referring to the Gantt Chart template bit.ly/automatic-gantt-chart, the start date is actually calculated based on the oldest date from the list of tasks, so when you mark tasks as completed a new start date will become the oldest date which alters the dates along the top automatically. The benefit here is that the Gantt Chart will automatically move along with active tasks so older completed tasks will not waste valuable space and you'll never need to add new dates at the end of the chart as this will dynamically hide older dates and show newer dates at the end.
Glad you found this helpful. You might be interested in this Project Management template as this has a built-in Gantt chart which can be filtered on Category/Project: www.spreadsheetwise.com/product-page/project-management-template 😉
Hi! I have bought the template and am having a problem with it. Despite the "Days" column counting network days only excluding the weekends, the Gantt chart auto-populates the network days for the weekends and fills them with color. How can I solve this? Without correct Gantt this template is not correct...
Hi @OlenaBryliak-s2s , I can provide a separate template that only displays working days on the Gantt chart, if this is of interest to you, please contact me via the website with your order number. Thanks!
Hi i jsut bought your project management template and the gantt chart is working well but it present all the tasks in gray color, and idk how to solve this problem !! can you help me
Hi @vabeeni, the Gantt Chart will show with colours if you have assigned tasks to a category, otherwise they will show in the default grey colour. Hope that helps 😉
hello - great doc and video! Quick question - I have an excel doc that is pretty elaborate. Do I try to import into your template or am I aligning columns and copy and pasting from my sheet into yours?
Hi @AndyLuce This template is only designed for Google Sheets, things will work slightly different in Excel. In terms of setup, you could go either way really, it depends on whether you want to rebuild your existing sheet into this or vice versa.😉
I purchase the template but as the status box is check, the project remains as opposed to automatically elliminating projects as the are checked off - how do I fix this?
Hi, ticking the status box will grey the line out and adjust the dates along the top of the Gantt Chart. The dates along the top only show active tasks (Not completed). You can hide rows in the table that are completed. This is shown in the video, so take another look if you're not sure how to filter rows out. This will mean you can view only outstanding tasks is you use filters in the table and the date along the top will relate only to open tasks.
This is wonderful:) thanks alot. I'm looking for something similar just for an hours gantt chart, is your template suitable for that as well? do i have to create a new sheet for every day?
Thanks for your comment! This Gantt Chart would not work for hours of the day in its current set up, but it could be achieved with some modifications to the conditional formatting and the start and end times as opposed to start and end dates. If you filtered out tasks that were completed then each day you would see the new tasks, but effectively if you had this set up just to look for hours then any uncompleted tasks would simply continue to show every day since it's not that date that matters, but the hour of the day. Perhaps I'll post a video on Gantt Charts for hours in the day rather than days in the week 😉
I don't know whats wrong but my custom formula doesn't work. Once I add new cells after comma inside brackets, it says invalid formula. for example =AND(G$1>=$D2) works ok, but if I continue adding like =AND(G$1>=$D2,G$1
@@spreadsheetwise I solved it with Chatgpt. I changed , to ; and it works now. I don't know why google sheet doesn't recognize the formula if it has , after $D2
Google Sheets uses commas and semicolons differently depending on the country you're in. Here is a useful help page on the topic: support.google.com/docs/thread/4271054/change-semicolon-to-commas-in-formulas?hl=en
Hi Paul, I have another template that excludes weekends, but including holidays is not easy as the dates can change every year, particularly depending on country. If you've purchased the Gantt Chart template from my website just drop me an email and I'll share the version without weekends with you.
This might sound ridiculous, but if I purchase this template, will I be able to just copy the original and use it multiple times for separate projects?
@@spreadsheetwise if I purchase your template (which I did) can I use it multiple times across many projects? Anytime I try to copy it, it throws the formulas off.
Hi @DylanClayton-y6z, you can make copies of the spreadsheet for different projects that you own and use, but this cannot be distributed to other parties. Are you trying to copy only the sheet within the same spreadsheet? In its current form, this would not be possible due to range references within the settings sheet, etc. Technically, it would be possible to achieve this but modifications would need to be made to make it work.
Hi @antoniodzlo6558, I may be off the point here, but I'll attempt to answer your question if I can. If you add a task with some date way into the future (beyond 3 months, for example) then this would not show in the Gantt Chart since the chart only shows up to 3 months, but as you mark tasks as completed and effectively hide them with filters, the dates along the top of the Gantt Chart will automatically collect a new MIN date from the task table. This will effectively change the start date and end date. No extra columns are actually added, but the dates will change as you hide completed tasks. Any task you create with a date greater than 3 months will eventually show up as it moves into the 3 month window. Also bear in mind, this Gantt Chart bit.ly/automatic-gantt-chart was designed in Google Sheets, so it may behave differently in Excel
This template bit.ly/automatic-gantt-chart excludes weekends in the day count column, but excluding the weekend dates along the top of the Gantt Chart itself would not be that straightforward and excluding holidays would be even more complicated. If you wanted to exclude weekends the best way to achieve this would be to use the IFS function to check the day of the week, but it's probably not required if you use the NETWORKDAYS function in column H.
The conditional formatting rules should update with the newly added rows automatically, but you can always open the rules up to double-check if you need to. 👍🏻
You can adjust the date format to MM/dd/yyyy easily, full details are included in the setup instructions. This template is built to display days and weeks, so it you wanted months this would require a unique setup. If you contact us via our website we can explore options for you.
I tried the “And” conditional formatting; however, I presently only have the projected End Dates. Without any end dates, the conditional formatting is applied to all cells in the first column. And if I do not put in a start date but only the end dates, all cells in every row have the conditional formatting applied. Is there a way to adjust the formula so that zero conditional formatting are applied if there are no start dates found?
Thank you for your message. The Start and Due dates are pivotal to this set up, so looking at weeks or months would require a different structure in the conditional formatting and the display of dates along the top. You could try the native Timeline feature inGoogle Sheets by going to Insert > Timeline and choosing the table of data to build a timeline, but I find this a bit restrictive personally.
You're Godsent! I have been battling with this particular issue and then realized that my first date was L5 instead of L4
Glad you found this helpful 😉
What a clear and concise teacher you are. Thank you!
That's very kind of you, thank you!
Oh my god thank you I have been trying to figure this out for ages and even chat gpt could not do it. Thank you
Glad I could help! 🥳
Thank you, I enjoyed this. Can you tell me more about, or share another video on how the check the status checkbox might affect the sheet and timeline? Also, how do you use and configure the Progress and Progress % columns?
Thanks for your questions reference the Automatic Daily Gantt Chart: bit.ly/automatic-gantt-chart
You can mark tasks as completed with the status checkbox which will grey out the row but this will still be displayed in the Gantt Chart. It's possible to hide completed tasks (rows) by using the filter in cell A4 to filter out TRUE values in column A, so that you will only see outstanding tasks in the table and in the Gantt chart.
In terms of the Progress and Progress % columns (I and J), you can manually add a percentage completed in column J and this will visually represent as a bar chart in column I for the chosen colour in the label dropdown in column B.
The Gantt chart in the Project Management template is slightly different as this updates the progress bar automatically based on the the number of net working days remaining for a given task.
bit.ly/project-management-template
I hope that helps 😉
Super helpful! I immediately purchased this template.
Thanks for your support @jaymeepadilla6856 I hope you enjoy the Gantt Chart template. 😊
@@spreadsheetwise do you have classes so I can learn from you? 🥹🥹
@jaymeepadilla6856 I would like to offer online classes. It's a work in progress.😉
@@spreadsheetwise HOW DO I ENROLL????
I would suggest subscribing to my website to get receive emails and news and announcements👍🏻
Very helpful thank you but I have a question: why when I change the starting the date , the dates on the timeline change also? is there any way to keep it fix and the timeline do not change every time when I change the stating date?
Thanks for your question @PamelaYammine. If you are referring to the Gantt Chart template bit.ly/automatic-gantt-chart, the start date is actually calculated based on the oldest date from the list of tasks, so when you mark tasks as completed a new start date will become the oldest date which alters the dates along the top automatically.
The benefit here is that the Gantt Chart will automatically move along with active tasks so older completed tasks will not waste valuable space and you'll never need to add new dates at the end of the chart as this will dynamically hide older dates and show newer dates at the end.
very helpful. do you know how to do this with project management dependencies? Linking tasks together by WBS would be so helpful
Glad you found this helpful. You might be interested in this Project Management template as this has a built-in Gantt chart which can be filtered on Category/Project: www.spreadsheetwise.com/product-page/project-management-template 😉
Hi! I have bought the template and am having a problem with it. Despite the "Days" column counting network days only excluding the weekends, the Gantt chart auto-populates the network days for the weekends and fills them with color. How can I solve this? Without correct Gantt this template is not correct...
Hi @OlenaBryliak-s2s , I can provide a separate template that only displays working days on the Gantt chart, if this is of interest to you, please contact me via the website with your order number. Thanks!
Hi i jsut bought your project management template and the gantt chart is working well but it present all the tasks in gray color, and idk how to solve this problem !! can you help me
Hi @vabeeni, the Gantt Chart will show with colours if you have assigned tasks to a category, otherwise they will show in the default grey colour. Hope that helps 😉
hello - great doc and video! Quick question - I have an excel doc that is pretty elaborate. Do I try to import into your template or am I aligning columns and copy and pasting from my sheet into yours?
Hi @AndyLuce
This template is only designed for Google Sheets, things will work slightly different in Excel. In terms of setup, you could go either way really, it depends on whether you want to rebuild your existing sheet into this or vice versa.😉
@@spreadsheetwise I asked for a refund please - are you getting my emails?
I just received an email from you a few minutes ago, but nothing before that, sorry.
I purchase the template but as the status box is check, the project remains as opposed to automatically elliminating projects as the are checked off - how do I fix this?
Hi, ticking the status box will grey the line out and adjust the dates along the top of the Gantt Chart. The dates along the top only show active tasks (Not completed). You can hide rows in the table that are completed. This is shown in the video, so take another look if you're not sure how to filter rows out. This will mean you can view only outstanding tasks is you use filters in the table and the date along the top will relate only to open tasks.
This is wonderful:) thanks alot. I'm looking for something similar just for an hours gantt chart, is your template suitable for that as well? do i have to create a new sheet for every day?
Thanks for your comment!
This Gantt Chart would not work for hours of the day in its current set up, but it could be achieved with some modifications to the conditional formatting and the start and end times as opposed to start and end dates.
If you filtered out tasks that were completed then each day you would see the new tasks, but effectively if you had this set up just to look for hours then any uncompleted tasks would simply continue to show every day since it's not that date that matters, but the hour of the day.
Perhaps I'll post a video on Gantt Charts for hours in the day rather than days in the week 😉
thanks for the response. I'll give it a try
I don't know whats wrong but my custom formula doesn't work. Once I add new cells after comma inside brackets, it says invalid formula. for example =AND(G$1>=$D2) works ok, but if I continue adding like =AND(G$1>=$D2,G$1
Hi @Firzj
Try adding a dollar sign before the F2, just like this: $F2="Blue"
=AND(G$1>=$D2,G$1
@@spreadsheetwise I solved it with Chatgpt. I changed , to ; and it works now. I don't know why google sheet doesn't recognize the formula if it has , after $D2
Google Sheets uses commas and semicolons differently depending on the country you're in. Here is a useful help page on the topic: support.google.com/docs/thread/4271054/change-semicolon-to-commas-in-formulas?hl=en
@@spreadsheetwise thank you. Makes sense now. I didn't even know some countries use , ; differently.
Hi, how can I remove weekends and add in holidays to this please? thanks, Paul
Hi Paul, I have another template that excludes weekends, but including holidays is not easy as the dates can change every year, particularly depending on country. If you've purchased the Gantt Chart template from my website just drop me an email and I'll share the version without weekends with you.
This might sound ridiculous, but if I purchase this template, will I be able to just copy the original and use it multiple times for separate projects?
I tried to set up the original table, based off of what you input, but it was off.
Hi @DylanClayton-y6z , I'm not sure what you mean?
@@spreadsheetwise if I purchase your template (which I did) can I use it multiple times across many projects? Anytime I try to copy it, it throws the formulas off.
Hi @DylanClayton-y6z, you can make copies of the spreadsheet for different projects that you own and use, but this cannot be distributed to other parties.
Are you trying to copy only the sheet within the same spreadsheet? In its current form, this would not be possible due to range references within the settings sheet, etc. Technically, it would be possible to achieve this but modifications would need to be made to make it work.
Hello thanks for you effort and work. If I add a task to futures, Excel does not automatically create columns. I'm a little lost here.
Hi @antoniodzlo6558,
I may be off the point here, but I'll attempt to answer your question if I can.
If you add a task with some date way into the future (beyond 3 months, for example) then this would not show in the Gantt Chart since the chart only shows up to 3 months, but as you mark tasks as completed and effectively hide them with filters, the dates along the top of the Gantt Chart will automatically collect a new MIN date from the task table. This will effectively change the start date and end date. No extra columns are actually added, but the dates will change as you hide completed tasks.
Any task you create with a date greater than 3 months will eventually show up as it moves into the 3 month window.
Also bear in mind, this Gantt Chart bit.ly/automatic-gantt-chart was designed in Google Sheets, so it may behave differently in Excel
Hi, this is a great video. How would I exclude weekends and holidays in my chart?
This template bit.ly/automatic-gantt-chart excludes weekends in the day count column, but excluding the weekend dates along the top of the Gantt Chart itself would not be that straightforward and excluding holidays would be even more complicated.
If you wanted to exclude weekends the best way to achieve this would be to use the IFS function to check the day of the week, but it's probably not required if you use the NETWORKDAYS function in column H.
@@spreadsheetwise Got it. Thanks for the reply!
does it apply my conditional formatting when I create more rows?
The conditional formatting rules should update with the newly added rows automatically, but you can always open the rules up to double-check if you need to. 👍🏻
Hello, how do I change the timeline to read US date style? month/date/ year. Also full month dates not weeks. Thank you!
You can adjust the date format to MM/dd/yyyy easily, full details are included in the setup instructions.
This template is built to display days and weeks, so it you wanted months this would require a unique setup. If you contact us via our website we can explore options for you.
MAGIC !!
No problem! 😉
I tried the “And” conditional formatting; however, I presently only have the projected End Dates.
Without any end dates, the conditional formatting is applied to all cells in the first column.
And if I do not put in a start date but only the end dates, all cells in every row have the conditional formatting applied.
Is there a way to adjust the formula so that zero conditional formatting are applied if there are no start dates found?
There is a similar error in the “Days” column. Fixed both with Embedded “If isblank formulas”
Glad you figured this out @barclaybryan9955 👍🏻
Glad you figured this out @barclaybryan9955 👍🏻
thanks for the Automatic Gantt Chart Template, i need help to make the Gannt chart based on months please
Thank you for your message. The Start and Due dates are pivotal to this set up, so looking at weeks or months would require a different structure in the conditional formatting and the display of dates along the top. You could try the native Timeline feature inGoogle Sheets by going to Insert > Timeline and choosing the table of data to build a timeline, but I find this a bit restrictive personally.
Thanks a lot! 😘
You're welcome 😊