How to skip non-business days for recurring tasks?

Posted on

When you have recurring tasks and one of the instances/occurrences happens to fall on a holiday, you may like to skip that occurrence. You may want to remove just that one occurrence and keep all the others. You can do that easily in our Task Manager (Advanced) Excel Template.  This article explains how to do that with a simple example.

 

If you are not familiar with the Task Manager template, please view the product page.

VIDEO DEMO

 

STEPS

Let’s start with entering a simple task as shown below.

Task Manager (Advanced) - Excel Template - Enter Task
Task Manager (Advanced) – Excel Template – Enter Task

 

A Daily task from 4th Jan to 15th Jan, with Mon-Fri selected. The template shows that this task generates 10 occurrences (arrow on the image).  Great, but let’s say we have a company holiday on 13th Jan and we would want this daily task not to occur on 13th Jan.

The REPORT sheet now shows that there is a task occurrence on 13th Jan.

Task Manager (Advanced) - Excel Template - Task Occurrence Report (before)
Task Manager (Advanced) – Excel Template – Task Occurrence Report (before)

 

First, let’s enter the holiday in the SETTINGS sheet.

Task Manager (Advanced) - Excel Template - Enter Holiday
Task Manager (Advanced) – Excel Template – Enter Holiday

 

Now, let’s go back to the CREATE_TASKS sheet and enter ‘Skip’ in the NON-BUSINESS DAYS column. As shown in the image below, now, the number of task occurrences will come down to 9.

Task Manager (Advanced) - Excel Template - Enter Skip in Non-Business Days
Task Manager (Advanced) – Excel Template – Enter Skip in Non-Business Days

 

This simple step will ensure the specific task does not occur on any weekends and holidays. The REPORT sheet will reflect the change.

 

Task Manager (Advanced) - Excel Template - Task Occurrence Report (After)
Task Manager (Advanced) – Excel Template – Task Occurrence Report (After)

 

If there are any questions about this feature or the template itself, please leave them in the comments. Thank you.

How to use Custom Columns in Task Manager (Excel Template)?

Posted on

This article will demonstrate how we can leverage the custom columns available in the Task Manager (Advanced) Excel Template. Though I use this specific template in this article, the concepts apply to other templates on indzara.com, where custom columns are used.

To begin with, what are ‘Custom Columns’? I use this phrase to indicate columns that are available in the template that the user can customize and use for his/her specific need. The templates are designed with necessary columns needed for the functioning of the template. But the user would want to add additional information that is unique to their business requirements. These custom columns are designed to meet that need. Let’s take an example with our Task Manager template.

The table where you create your tasks has several columns and the first few are shown below.

Task Manager (Advanced) Excel Template - Create Tasks
Task Manager (Advanced) Excel Template – Create Tasks

 

There are more columns but there isn’t one called PROJECT NAME. However, in your business scenario, you would want to tag each task to its project. Similarly, different users have different needs for additional columns. If you are familiar with Excel and Excel Tables, you would easily add additional columns. However, I try to make my templates accessible for even those who are new to Excel. Hence, I provide ‘Custom Columns’ in tables where the user can just start typing their data.

In this article, we will see how to do the following:

  1. Enter Notes for task occurrences and make them appear on Dashboard and Report automatically
  2. Make task occurrence’s status appear on Dashboard automatically
  3. Enter Project Name at task level and make it appear on Dashboard and Report automatically

 

To become familiar with the structure and set up of the template, please visit the product support page for Task Manager (Advanced) Excel Template.

 

  1. Adding Notes or Comments

The UPDATE_TASKS sheet where we update the status of task occurrences has two columns (CUSTOM COL 1 and CUSTOM COL 2) – as shown below.

Task Manager (Advanced) Excel Template - Update Tasks - Custom Columns
Task Manager (Advanced) Excel Template – Update Tasks – Custom Columns

 

We can rename the column name CUSTOM COL 1 as NOTES (or anything you would like to name). Then, we can enter our notes in that column. I have shown below a sample.

Task Manager (Advanced) Excel Template - Update Tasks - Adding Notes
Task Manager (Advanced) Excel Template – Update Tasks – Adding Notes

 

Now, when we see the DASHBOARD, the NOTES column will appear.

Task Manager (Advanced) Excel Template - Added Notes - Dashboard
Task Manager (Advanced) Excel Template – Added Notes – Dashboard

 

Similarly, on the REPORT sheet, the NOTES column will appear.

Task Manager (Advanced) Excel Template - Added Notes - Report
Task Manager (Advanced) Excel Template – Added Notes – Report

 

I have used Notes/Comments. You can use it to store any type of information at the task occurrence level.

 

2. Displaying STATUS on Dashboard

At the task occurrence level we can assign a status. If we want to display that on the Dashboard, we can do so easily with a simple formula.

First, let’s rename the CUSTOM COL 1 as TASK STATUS.

In cell I13 (first row in UPDATE_TASKS table), type ‘=’ and then use mouse to point to cell E13. Excel will now create a formula that looks like what is shown in the image below. Then, press Enter key. Now, Excel will apply this formula to all the cells in the column.

Task Manager (Advanced) Excel Template - Status - Formula
Task Manager (Advanced) Excel Template – Status – Formula

 

Now, the TASKS STATUS column will show the STATUS of the task occurrences.

Task Manager (Advanced) Excel Template - Status - Custom Column updated
Task Manager (Advanced) Excel Template – Status – Custom Column updated

 

DASHBOARD now shows the TASK STATUS.

Task Manager (Advanced) Excel Template - Dashboard - Status
Task Manager (Advanced) Excel Template – Dashboard – Status

 

Similarly, you can point the formula to any column in the same table.

 

3. Adding Project Name to the Task and make it appear on Dashboard/Report automatically

This one is slightly more advanced than the previous items, since we will be writing a longer formula. 🙂

First, let’s use the custom columns available in the CREATE_TASKS table.

Please note the difference.  Here, we use the CUSTOM COL 1 at the Task level. In the previous 2 examples, we used the CUSTOM COL 1 in the Task Occurrence level.

Task Manager (Advanced) Excel Template - Project Name to Task
Task Manager (Advanced) Excel Template – Project Name to Task

 

Then, we go to the UPDATE_TASKS sheet and enter a formula in cell I13 (CUSTOM COL 1 renamed as PROJECT NAME). This formula brings the Project Name from the CREATE_TASKS table to UPDATE_TASKS table for the corresponding Task ID.

Task Manager (Advanced) Excel Template - Formula in UpdateTasks
Task Manager (Advanced) Excel Template – Formula in UpdateTasks

 

Excel will apply the formula to all the cells in the column automatically.

Task Manager (Advanced) Excel Template - Project Name in UpdateTasks
Task Manager (Advanced) Excel Template – Project Name in UpdateTasks

 

Dashboard:

Task Manager (Advanced) Excel Template - Project Name - Dashboard
Task Manager (Advanced) Excel Template – Project Name – Dashboard

 

Report:

Task Manager (Advanced) Excel Template - Project Name - Report
Task Manager (Advanced) Excel Template – Project Name – Report

 

This is how easily we can extend the functionality of the template. If you have any specific questions about the template, please leave them in the comments and I will be happy to respond.

Announcing v2 of Task Manager (Advanced) Excel Template

Posted on

Merry Christmas and Happy Holidays to Everyone.

I am very glad to announce that v2 of Task Manager (Advanced) Excel Template  is now available. As always, existing customers get the upgrade for free. An email will be sent along with the new file. Thanks for your feedback.

New users can purchase the template from the product page.

Key Enhancements made in v2

  • Increased Functionality
    • Added 6 new Frequency types (Last Day of Month, Every Nth Business Day, Nth Business Day of Month, Last Business Day of Month, Nth Business Day of Week, Last Business Day of Week)
    • Now handles business days (weekends and holidays can be customized)
    • Increased task occurrences to 1200
    • Custom Column (which you can decide what to use for) now displays in Report and can be used for filtering
    • Dashboard now shows 40 pending occurrences along with Custom column
  • Time Saving Features
    • Assign task to a resource and all the occurrences will inherit automatically. This will save time by reducing data entry.
      • You can still change the assignment at occurrence level if needed
    • Now all tasks are active by default. Deactivate by entering Y when needed. This will save time as well.

If you have any questions, please leave them in the comments. Thank you.

Task Manager (Advanced) – Excel Template – Support

Posted on

This page will host support material for the Task Manager (Advanced) Excel template.

This Excel template is designed to help create and update status of tasks easily. This can be used by project managers who handle multiple tasks and manage multiple team members. This can also be used by individuals to manage personal tasks.

SUPPORT ARTICLES

REQUIREMENTS

You need a copy of Microsoft Excel to open this file. The product has been tested in 

  • Microsoft Excel 2010 or above for Windows 
  • Microsoft Excel 2011 or above for Mac


FEATURES

  • Handles 100 active tasks at a time
  • Create one-time or recurring tasks (12 choices of task types)
  • Easily choose weekdays for each recurring task
  • Handles 366 days of task occurrences at a time
  • Handles 1200 task occurrences (If you have a task that repeats weekly for 4 weeks, it is 1 task but 4 task occurrences)
  • Option to assign each task occurrence to one resource 
  • Allows up to 25 resources in total
  • View/update status of each occurrence
  • Customize and manage your own (up to 7) status values
  • Instant view of pending tasks
  • Simple calendar view of tasks in any week
  • Task occurrence status shown with color formatting
  • Dashboard with summary statistics

 

VERSION HISTORY

  • v1 Oct 2014
  • v2 Dec 2015

 

 If you have any questions about this Excel template, please leave a comment and I will respond as soon as I can.  Thank you.

 

HOW TO USE THE TEMPLATE

 

OVERVIEW OF STEPS

  1. SET UP: In the SETTINGS sheet, enter your own custom status values if preferred. Choose which status values should be considered as Pending tasks.
    1. Enter the list of resource names tasks can be assigned to
  2. In the CREATE_TASKS sheet, enter tasks
  3. In the UPDATE_TASKS sheet, update the tasks of task occurrences
  4. In the DASHBOARD sheet, view pending task occurrences, tasks due in a calendar week and summary statistics of tasks in any date range of your choice.
  5. In the REPORT sheet, view all task occurrences and their status.

DETAILED STEPS

SETTINGS

Enter required information in Settings.

Task Manager (Advanced) Excel Template - Status Values
Task Manager (Advanced) Excel Template – Status Values

 

  • 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.

 

Choose Weekends

Task Manager (Advanced) Excel Template - Weekends
Task Manager (Advanced) Excel Template – Weekends

 

Enter holidays if any

Task Manager (Advanced) Excel Template - Holidays
Task Manager (Advanced) Excel Template – Holidays

 

Enter the list of names tasks can be assigned to. These are the names that will show up in the DASHBOARD sheet where you can view statistics. Please do not leave a blank row in between names.

Task Manager (Advanced) Excel Template - Resources
Task Manager (Advanced) Excel Template – Resources

 

CREATE TASKS

 

Task Manager (Advanced) Excel Template - Create Tasks
Task Manager (Advanced) Excel Template – Create Tasks

(For general tips on entering data in Excel tables, please visit my video on YouTube)

 

  • Enter Task ID and Task Name
  • Enter Start Date, Freq Type and End Date.
Task Manager (Advanced) Excel Template - Create Tasks
Task Manager (Advanced) Excel Template – Create Tasks

 

  • Choose the weekdays that you would like the tasks to be due.
  • Enter N where applicable.
  • Assign each task to a resource

 

Task Manager (Advanced) Excel Template - Create Tasks
Task Manager (Advanced) Excel Template – Create Tasks

 

  • To not create instances on non-business days (holidays and weekends), choose ‘Skip’ in Non- Business Days column.
  • Only the first 100 active tasks will be used in the template to create task occurrences. Enter Y in the ‘Deactivate?’ column to deactivate a task.
  • Custom columns are available and they could be used to store any information you would like for tasks.

 

Task Manager (Advanced) Excel Template - Create Tasks - Error
Task Manager (Advanced) Excel Template – Create Tasks – Error

 

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.

 

Task Manager (Advanced) Excel Template - Mini Calendar
Task Manager (Advanced) Excel Template – 2016 Mini Calendar

 

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.

Task Manager (Advanced) Excel Template - Frequency Types
Task Manager (Advanced) Excel Template – Frequency Types

 

Examples

  • 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

 

UPDATE TASKS

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.

Task Manager (Advanced) Excel Template - Task Lookup
Task Manager (Advanced) Excel Template – Task Lookup

 

Steps to Update Task Occurrences

Task Manager (Advanced) Excel Template - Update Tasks
Task Manager (Advanced) Excel Template – Update Tasks

 

  • Enter Task ID. Task Name 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.

 

Task Manager (Advanced) Excel Template - Update Tasks
Task Manager (Advanced) Excel Template – Update Tasks

 

  • (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.

 

DASHBOARD

Dashboard sheet provides the first 40 pending task occurrences.

Task Manager (Advanced) Excel Template - Pending Tasks
Task Manager (Advanced) Excel Template – Pending Tasks

 

The Calendar view provides the 7 day view of task occurrences and their status values. They are color coded for easier understanding. You can change the FROM date and view any 7 consecutive days at a time.

Task Manager (Advanced) Excel Template - Calendar
Task Manager (Advanced) Excel Template – Calendar

 

Summary statistics on task occurrences are provided. You can customize the date range easily by modifying the TO date.

 

REPORT

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.

Task Manager (Advanced) Excel Template - Report
Task Manager (Advanced) Excel Template – Report

 

 

You can filter the table to only Pending task occurrences by choosing TRUE in the PENDING field filter. You can filter to task occurrences of specific task by filtering on Task ID. You can filter to task occurrences in a specific date range by filtering on DUE DATE field. 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).

 

TaskManager_Report_FilterPending

Task Manager Excel Template v2

Posted on
I have made the following enhancements to the Task Manager Excel Template and the Task Manager v2 is now available

1) I have added an additional option for Task type. v1 had five choices: One-time, Daily, Weekdays, Weekly and Monthly. v2 has an additional option: Every 4 Wks. This allows you to schedule recurring tasks that repeat every 4 weeks. 2) Also, I noticed that the file linked on the page was referring to a working version of the template that didn’t have proper documentation. That has been fixed now with v2. 

3) The template now has no sample data in it. So, you can start entering your data right away. I have provided a separate document with the sample data for reference. 

Please take a look at the updated page and provide your feedback.