Simple Method to create Excel Gantt Chart with Dynamic Task Bar and Holidays Marker
Вставка
- Опубліковано 12 вер 2024
- This video will show you a simple method on creating Gantt Chart with Non Working Days and Holidays exclusion for your project using Microsoft Excel. The method will show you workdays task bar that will skip non working days as well as particular holidays in its table. And respective employee's workdays will be calculated automatically without those non working days.
This method is proven to be the easiest and the quickest one to be understood by any people with different excel skill level.
The main logic is to put value of 1 (it could be any values) for workdays task bar and other values for non working days. In this video I used value 0 for unused days, 1 for workdays, 2 for weekly non working days, and 3 for particular holidays. All values are wrapped within single formula in Gantt Chart table. Then, set conditional formatting formula to show color will be as easy as set specific colors to those values in each Gantt Chart cells.
And counting the number of workdays will be as easy as count the occurrence of value 1 in respective rows.
After seeing this, you can develop your Gantt Chart to have other values for other conditions. Or you can colorize specific dates in workday task bar to have different color, for example for showing current progress etc.
Super tutorial. I was wondering how to account for non working days and holidays. Thank you so much 👍😎🇦🇺
... brilliant ... such clear tuition ... thank you 🙏xx
Awesome! Thank you for sharing!
Great job, it helped me a lot !!!! - Thank you so much !!
Great gantt chart! Is there a way to have the start date and the duration as the inputs and have the end date as the output while also taking into consideration non working days?
So much help. Thanks a lot!
Great Job! Thanks a lot :D
Could you tell me why we have to plus 1 in the formula "=C6-C5+1" ?
Could you please help me with a worksheet that im working it, im trying to put the holidays but is not working
Thank you
Sangat membantu.. terimakasih..
tapi gimana kalau setiap task harus diselesaikan setelah task berikutnya? Bagaimana kalau non working days nya banyak?
Hi, really nice tutorial. pls adivse how to create week view/ or month view instead of day view in this?
Hi Sazid.To have a week view, you can shrink all date columns, create additional rows on top of those dates, merge every seven cells in that rows and put Week title. BR.
Dear When you Add Holidays your Actual days calender is same which is wrong . you mush have the same working days with actual days you required .. thanks
Does this gantt chart take account of the days which have been completed? I am looking for a formula which shows the tasks or days which have been completed in a different colour but I don't seem to achieve this. Would you have a suggestions?
hi Sarah, you can write the formula with today's date or any dates (create a new column) as project end date and assign new number and new color for those formula. You can duplicate the start-end date comparison formula where the end date refers to other column. Regards.
G13 did say 7 days before holidays were added cutting down that task time. When really it should roll it over past the holiday and weekend. I need to shift project work when my team has time off. Anyone help?
Cell H9:CT9 are all showing by day, how to group the day into month?
it is possible to add more than 2 official holiday table?
Yes. It is possible. Just define another number to those holiday tables and put it into the formula. Then, you can set particular colors for those numbers to get different color for different holiday table.
What if you change year (let's say a project lasts from November 2017 until February 2018?)
The formula works with date reference. In the date titles, If you drag the dates to the right, dates will increase automatically. Since the values that will be compared are dates, the formulas will compare with year included in the formulas as well. BR.
Had error message on the formula and tried many things.
First you need on more recent excel version to change the "," by ";"
You also need to chek that all your cells are in date format.
And to finish, you have to set the formula in your language, i.e for me (french)
=IF(AND(H$9>=$E10,H$9=$E10;H$9
Hi Vincent, it requires excel 2007 for windows and above. Also, its function name and format have to be adjusted with your own windows and country setting. Best regards
hi there I have tried and Write the code =if(and(H9>=E10,H9
Hi, it could be caused your excel uses different format for ",". Try to replace it with ";". Regards.
hi there where should I put "," ??=if(and(H9>=E10,H9
I am using excel 2016
hi again I have replace "," With ";" now it Writes (#name`?)what do I wrong
=if(and(M$9>=$E11;M$9