My first thought upon moving from SE to HR was, “Let’s manage man-hours! I thought, “Let’s manage man-hours!

To talk about man-hours, let’s first figure out our own man-hours! That’s why I love man-hour management.

I would like to show you how to manage man-hours using the template and WBS that I always use.  

Ver 2.4 is the latest version!

Please watch the video below to learn how to use it! Sorry, the following video is in Japanese..


What is WBS…?

WBS is an abbriviation for Work Breakdown Structure, a project management tool. It is a tool to visualize tasks by subdividing them into small pieces. As will be introduced in more detail later, tasks are subdivided and assigned to the person in charge, as shown below.

WBS is often combined with a Gantt chart. A Gantt chart is a kind of graph in which periods are colored to make them easier to read. (The Gantt chart is the area with the schedule on the right side of the above image.)   The good thing about the WBS is that it shows in detail what kind of work is required for each output and how many man-hours it will take. The good thing about WBS is that you can manage in detail what kind of work is required for each output and how many man-hours it will take for each person in charge. This is a perfect tool for me, who loves details.  


Download WBS

You can use the following WBS English version. Please download it!!


How to use WBS (For Administrator)

1. enter the start date of the Gantt chart in cell O6!

The first step is to enter the start date in cell O6. If you want to manage from December 2021, enter “2021/12/1” in cell O6.


2. identify the tasks!

When using WBS, we start by identifying tasks. We identify tasks to ensure that there are no omissions toward the goal.


3. assign responsibilities!

Assign the person in charge to the item for which he/she is in charge. Be careful that the person creating the WBS does not appear more often as the person in charge. Consider better assignment of the responsibilities so that the project will run smoothly.


4. list your schedule!

Now, in order to manage the schedule, it is essential to manage the forecast. First, be aware of the due date of each task and enter the duration.   After entering the date of the scheduled period, the Gantt chart will be filled with a light yellow color. This makes it possible to visually confirm the scheduled period.

Although it is optional, it is also recommended to enter the number of man-hours planned. By identifying tasks with an estimate of how many man-hours it will take, you can control the tasks smoothly. It is also recommended that each person in charge be aware of the number of man-hours required.   At this point, your work as an administrator is complete. If more tasks are added, please add new lines, etc.  


How to use WBS (Person in charge)

1. enter the start date of your results!

Check the tasks assigned by the administrator. If the scheduled start date is before today (Today) and no actual date has been entered, the status will show “Not entered”.  

※Today is 2021/06/23.

If you are Mr. Sakurai(櫻井) and you started work on 7/1, you would enter “7/1” as the start date of your achievements. Then the status will change to “Working”. This will allow you to see at a glance which task you are currently working on.


2. enter the actual number of man-hours!

An important part of the WBS is understanding man-hours. How much time is spent on that work…! This is the real purpose of figuring this out…!   Now, where should the person in charge enter the man-hours… HERE!


Enter the number of hours (h) in the Workday column and in the row for your task. For example, 0.5 for 30 minutes.


Compared to the previous image, the difference time from the schedule is also 0.5 hours less. I have been trained in this management method and have been working with the mindset of “I’m going to make sure I have enough time left over for the scheduled variance! I hope you will try to remember to input the man-hours.


3. enter the end date of the achievement!

When you have completed your work, enter the date in the End Date column. Here, I entered 7/2.

The status has been changed to “Completed” and the Gantt chart for 7/1 and 7/2 actually worked on is filled in orange. That’s one task done! Thanks for your hard work!  


Insert Row (from Ver. 2.1)

To insert one line

If you want to insert one additional row, select the row you want to insert.

After click the insert button, you can insert a row, as shown below.

As formulas are entered in some columns, it is convenient to click the Insert Row button.


To insert multiple lines

You can insert that number of rows by selecting more than one and clicking the insert Rows button.

↑ It’s a little confusing, but 5 lines have been inserted.

About column settings

The number of columns in the Gantt chart can be inserted at any time. However, for formulas, the program will set them automatically. Here, the maximum is FO columns.

Since it is troublesome to modify the program every time, we have prepared a Settigs sheet so that users can change the settings at their discretion.

When you add a column, please change cell B1 on the Settings sheet.


Color the cells depended priority (from Ver. 2.3)

You can now set the priority of coloring from the Settings sheet option “Set Priority for Weekends and Holidays”.

As a sample, we have chosen to span October 8-10.


↓If you select “Saturday, Sunday or Holiday”


↓ If you select “Planning”


Option to know the planned schedule upon completion (from Ver. 2.4)

The schedule dates can now be tracked upon completion from the “Schedule Priority Settings” option on the Settings sheet.

Without this option, as shown below, it is not clear whether a task was originally assigned (scheduled) on 1/11. As you can see by comparing the two images below, when the option is set, the schedule of achievements is filled in slightly orange, and achievements that were not scheduled can be set to a slightly darker color and with a darker dotted line.

If you do not set the schedule priority option
If you do set the schedule priority option


In the end

Task and man-hour management is very important for any type of job! Please try to utilize WBS for better project management. By the way, it’s not a project, but man-hour management in your daily work is no problem at all, so please give it a try!  

My senior gave me a copy of Shuwa System’s Gyaku-Yaku Daizenshuu. I have been using this book for about 10 years. If you want to start VBA, you should definitely have this book! (I use both Excel and Access…….)


Update history

Version 更新日 内容
1.0 2021/6/23 First eddition
2.0 2021/12/26
  • Adjustment of Gantt chart colors and ruled line types
  • Added message when entering
  • Changed start date to 12/1/2021
  • Changed extension to “.xlsm
2.1 2022/5/23
  • Added Insert Row macro (on Settings sheet)
  • Changed start date to 5/1/2022
2.2 2022/6/10
  • Added user-defined function to status, modified to be available in older Office versions
  • Changed start date to 2022/6/1
  • English version released (2022/07/05)
2.3 2022/10/4
  • Changed the setting so that it can be arbitrarily set whether priority is given to color-filling on Saturdays, Sundays, and holidays, or to the date of the preliminary results.
2.4 2023/01/01
  • Optional settings changed to visually show the difference in performance from the original schedule for completed tasks