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

## Excel task planner main features

• set task frequency in days, weeks, months, years
• sets next due data based on the actual completion date and set frequency
• override the next due date manually
• color codes overdue task in red, tasks due today in yellow
• auto-sorts tasks by due date (next due always come first)

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 2. Create a shape for the button

Insert a shape in the first open column in the same row with 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 header for the macro to work (see 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 subsection below to understand the macro steps.

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

``````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, copy 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 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
"E2:E27"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Schedule").Sort
.SetRange Range("A1:E27")
.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 color coding

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

### Step 10. Test your planner

In this step, if everything was done right, your planner should work. Add tasks as per your needs and setup their frequency and due dates. Click on 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.