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 😉
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 😉
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.
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.
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.
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 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
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.
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?
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
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. 👍🏻
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.
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 😉
What a clear and concise teacher you are. Thank you!
That's very kind of you, thank you!
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 😉
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👍🏻
Thanks a lot! 😘
You're welcome 😊
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. 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 😉
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.
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.
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.
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.
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.
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.
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 👍🏻
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
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. 👍🏻
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!
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