Task Planner Excel

Learn how to make your own task planner in Excel. This planner will help you keep track of all your recurring tasks. Keep track of your screen cleaning, house chores, car maintenance, house maintenance tasks in this handy task planner in Excel.

The best part about this planner is that it will keep you on track. Even if you are late for doing one of your chores, it will set your next due date based on when you actually completed the task.
No more guessing, no more getting overwhelmed with tasks that keep showing up on your list based on fixed schedules.

In this tutorial, you’ll get all the steps on how to create your own planner in Excel.

Excel Functions used in this example

This example uses the following Excel functions and features

Click on the links to learn more about each of these concepts

Excel task planner’s main features

  • set task frequency in days, weeks, months, years
  • task button to mark a task completed
  • logs every task completed in a task log
  • sets next-due data based on the actual completion date and sets frequency
  • override the next due date manually
  • colour codes overdue task in red, tasks due today in yellow
  • auto-sorts tasks by the due date (next due always come first)
  • add new tasks by copy-pasting existing column

Here is a video on how it works.

At the end of this tutorial, if you want to bypass all the work and start using it immediately, you’ll have the option to do so.

Step 1. Create the first task

Start with a tab called “Schedule”. Create a header as per image below and write your first task.

Step 2. Create a shape for the button


Insert a shape in the first open column in the same row as your task. Do not create multiple tasks or buttons at this point. One is sufficient. They can be copied and pasted once it is complete

Step 3. Setup drop-down list for set periods

Create a new tab called “Log”. In an empty column, create a list of periods you want in your drop-down (days, weeks, months, years). Name the range “Periods”

In the first tab, “Schedule” set Data Validation on the first column to be “Select From the list” and add Periods as the name of the list

Enter the following formula in the Number of days cell. All calculations will be done in days. The calculated value for number of days will be based on the user selection of periods and the number of periods ( 1 week = 7 days, 3 months = 90 days etc)

=IF(D2="days",C2,
    IF(D2="weeks",C2*7,
         IF(D2="months",C2*30,
              IF(D2="years",C2*365,C2)
            )
       )
    )

Step 4. Finish the log tab

In the log tab, create the following header. Do not merge any cells. First, two rows have to be headers for the macro to work (see the macro in the next section)

Step 5. Start creating the macro

Copy paste the macro in the section below in the Module 1 section. Follow each of the subsections below to understand the macro steps.

5A. Save the task and the completed date as of today

Sub TaskComplete()

Sub TaskComplete()

Application.ScreenUpdating = False
Sheets("Schedule").Select
ActiveSheet.Range("A" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Select
Selection.Copy
Sheets("Log").Select
Range("B1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

5B. Set the next due date based on today

Sheets("Schedule").Select

ActiveSheet.Range("E" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Select

ActiveCell.FormulaR1C1 = "=TODAY()+RC[-3]"

5d. Fix the due date

    Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Full Macro Code

This is the full macro code, copied in Module 1

Sub TaskComplete()

Application.ScreenUpdating = False
Sheets("Schedule").Select
ActiveSheet.Range("A" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Select
Selection.Copy
Sheets("Log").Select
Range("B1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

ActiveCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Schedule").Select

ActiveSheet.Range("E" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Select

ActiveCell.FormulaR1C1 = "=TODAY()+RC[-3]"
    Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub

Step 6. Link the macro to the button

On the button you created, right click and Assign Macro “TaskComplete”

Step 7. Setup automatic sorting VBA code

In the WorkSheet Schedule, add the following code.

This code will auto-sort your columns by the due date every time anything changes in the worksheet (when completing a task, updating a due date etc.

Private Sub Worksheet_Change(ByVal Target As Range)
    Columns("A:E").Select
    Range("E1").Activate
    ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Add2 Key:=Range( _
        "E2:E27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Schedule").Sort
        .SetRange Range("A1:E27")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

Step 8. Copy Rows

Select the entire row and right-click Copy. Select the first empty row, right click and Paste. This will create as many copies of the task as you need. This will also copy the buttons with the attached macro.

Step 9. Setup the colour coding

In column E, (the Due Date) set the following rules in Conditional Formatting

Step 10. Test your planner

If everything was done right, your planner should work in this step. Add tasks as per your needs and set up their frequency and due dates. Click on the Complete button to test.

When clicking Complete, the due date of the task should update to the next date from today based on frequency. The task should also be recorded in the Log.

How can I download the file?

If you want to skip all this work and, for the price of a coffee with a friend, download a fully working planner, visit our Etsy or TeachersPayTeachers site

If you had any problem implementing these instructions, please leave a message.

Comments are closed.