Task Manager - Advanced (Excel) Step by Step User Guide
Modified on: Mon, 3 Jul, 2023 at 7:12 PM
OVERVIEW OF STEPS
SET UP: In the SETTINGS sheet, enter your own custom status values if preferred. Choose how you'd like to customize your Kanban board.
In the CREATE_TASKS sheet, enter tasks
In the UPDATE_TASKS sheet, update the tasks of task occurrences
In the SUMMARY sheet, get a quick overview of your task' completion by by month and resource.
In CALENDAR sheet, view the monthly and weekly overview of tasks.
In the RESOURCE VIEW, Gain insights into your project's resource allocation with the resource level view.
In KANBAN BOARD sheet, view the the tasks as cards under each stage.
In the REPORT sheet, view all task occurrences and their status.
The PENDING sheet, displays tasks that are currently pending for immediate action.
Enter required information in Settings like the Start date from which you'd like to set tasks; list of holidays and Weekends as defined by your organization.
Enter the task status values and define on how each of these status should be reflected (whether pending or not).
In the Settings sheet, enter PERIOD START date.
Task occurrences will be scheduled from this date. The template can handle up to 1 year of task occurrences at a time.
Enter your own custom status values if preferred. Otherwise, leave the default values.
Choose which status values should be considered as Pending tasks.
A pending task is a task that is due by yesterday, with either no status or a status value that is considered as pending.
For example, in the template, 90% Complete, 50% Complete, 10% Complete, On Hold and Unknown are all status values that are considered as pending. If a task occurrence has one of those status values, then the template will list the task occurrence as pending.
For example, in the template, ‘Completed’ and ‘Skipped’ are status values that are not considered as pending. So, if a task occurrence has one of those two status values, then the template will not list the task occurrence as pending.
Colors cannot be modified here.
Project and Project Category:
Enter the list of projects and their categories in the settings sheet.
Also, enter the number of stages each of these tasks will go through, for the Kanban board settings.
Next item in the Settings sheet is the Kanban board settings i.e. the list of items that you'd want to be viewed in a Kanban board. You can customize up to 6 items here.
Ensure that there is no red border around the task, since that means there is an error. Usually, the error is due to not having a start date or having an end date that is before the start date.
A mini-calendar is provided in this sheet to help with choosing dates for tasks. You can change the month and year of the mini-calendar. Calendar is created using formulas and so please do not edit the cells.
FREQUENCY TYPES You can choose from 12 frequency types.
One-time: A ‘One-time’ task that occurs on Start Date
Daily: A task that occurs only on Mondays, Tuesdays and Wednesdays
Weekly: A task that happens on Saturdays and Sundays but happens only every 2 (N is 2) weeks
Monthly: A task that happens on 5th of a month but only if it is between Tuesday and Friday
Nth Wday of Mth: A task that occurs on 2rd (N is 2) Saturday and Sunday of a Month
Last Wday of Mth: A task that occurs on last Monday of a Month
Last Day of Month: A task that occurs on last day of each month.
Every Nth Business day: A task that occurs once every 3 (N is 3) business days from the Start Date of the task
Nth Business day of Month: A task that occurs on 3rd (N = 3) business day of a month
Last Business day of Month: A task that occurs on last business day of month
Nth Business day of Week: A task that occurs on 1st (N = 1) business day of a week
Last Business day of Week: A task that occurs on the last business day of a week
At the top of UPDATE_TASKS sheet, there is a quick look-up for Task details. You can enter a Task name and instantly see the task’s details such as Task ID, Start Date, End Date and Freq Type. Please do not edit formulas.
Steps to Update Task Occurrences
Enter Task ID. Task Name, Project, Project category and Assigned to will auto-populate.
Choose the due date from the drop down options. The template lists the possible due dates for that task and provides them in the drop down to make it easier for the user.
Enter a Status for that task occurrence.
Enter the Stage for the Kanban to board to correctly populate.
There are additional columns as well like :
(Optional) Enter priority of the task occurrence
(Optional) After the task occurrence is completed, enter Completed Date.
(Optional) Choose a person that the task occurrence is ‘Re-Assign to’, if the resource assignment has changed.
There are two additional columns available where you can enter any information you would like (Optional) to track for each task occurrence.
This summary shows the performance by Month and by Resource are also provided in Task Manager Excel Template.
The Calendar view provides a Monthly and a 7-day view of task occurrences and their status values. They are color coded for easier understanding. You can change the month and week as per your preference. You can view a list of 10 tasks at a time for each day in the Monthly view.
This template generates a Kanban board to efficiently manage your workflow.
The board shows the number of tasks that are in each stage for an overview to the left of the stage name. Below each stage, view each card under it with the customized list of items. The cards are color-coded based on their status.
Report sheet lists all task occurrences and their status. You can filter the table by the fields shown. Please do not sort the table because that would modify the calculations and will lead to incorrect data.
In this sheet have a quick view of only the Pending tasks.
A task occurrence is flagged as pending, if the due date is prior to today’s date and the status of the task occurrence is one of those that has been set to be considered as pending (in the Settings sheet).