Recently browsed foreigner's website，Discover an example of a well-crafted Gantt chart template，Share with readers and friends，The appearance is as follows。
Download link is provided at the end of the article。
▲authorJon Wittwer
Observe carefully，This template has the following features：
- Support task classification，Tasks can be divided into several broad categories as needed
- Support subtasks，Multi-level subtasks can be set as needed
- The progress of a single task is visually displayed in the form of a data bar
- Gantt chart made with conditional formatting，Modification date and completion progress are automatically updated
- Calendar view organized by work week
- Highlight today's date
01
Work week calendar view
(project management template for website development)Let me highlight this calendar view organized by work week：
(project management template for website development)
Link scrollbar toH4Cell，Display only after specified by scroll bar in the entire sheet view range8Gantt chart for working weeks。
The advantage of this is that if the project cycle is longer，Skip back through the Gantt without dragging the bottom right scroll bar。
(project management template for website development)
02
(project management template for website development)
Production method introductionTell your friends how to make this template。
If you also want to make such a template by yourself，You need to be familiar with the followingExceluse skills：
- function formula
- custom cell format
- Conditional Formatting
▎Set start week by formula
A more ingenious point is to set the starting week through the formula。
To ensure that each work week is complete in the calendar view7sky,and from Monday，used hereWEEKDAYfunction。
WEEKDAYfunction
Returns the day of the week corresponding to a date。by default，the number of days is 1（Sunday）arrive 7（Saturday）integer in range。
grammar
(project management template for website development)
WEEKDAY(serial_number,[return_type])Serial_number required。a serial number，Date representing the day the lookup was attempted。
(project management template for website development)
Return_type optional。the number used to determine the type of the return value。
It is not difficult to understand hereK6The meaning of the formula in the cell：
=C4-WEEKDAY(C4,1)+2 Calculate the first day of the first work week
+7*(H4-1) calculate the firstH4first day of the week
K4:Q4Merge cells to calculate with7The function for the working week of the interval is：
(project management template for website development)="Week "&(K6-($C$4-WEEKDAY($C$4,1)+2))/7+1
the first7The week number is displayed on the line using theCHOOSEfunction：
In fact, it can also be achieved by customizing the cell format。
▎Set date display with custom cell format
EList，FColumn and No.6The dates in the row are displayed specially by setting the cell format，E.gE、FColumns that display both the week and the date can be formatted via the following cell：
And the one just mentioned in the previous section7The date in the row cell is displayed as the week, which can also be achieved by customizing the cell format.：
the way is：Set the cell format to customizeaaaorAAA
In this way, the result will be displayed in the animation, and the set date format will be modified to a、two、three、Four、Fives、six、day。
(project management template for website development)
▎Setting a Calendar Gantt Chart with Conditional Formatting
How to Conditionally Format Gantt ChartsExcelI have shared before，Check out the conditional formatting settings for this template below：
It is also mainly calculated by formula calculation method to calculate the completed date and unfinished date of the current row，Set the format according to the formula calculation result of the conditional format。
If the application mechanism and setting method of conditional formatting are not well understood，You can refer to this article by Xiaobian：
Excel：This is how I understand how conditional formatting works
03
Project Management Template
ExcelThe project management template shared before basically uses the same template as the one shared aboveExcelTechnology，Just a little moreVBAProgrammable automation functions。
Students who need the project management tool template collection can pay attention first and then privately message me
For more exciting content, please click“understand more”
