How to Create Real Time Tracker in Excel (with Detailed Steps)

Watch Video – Create Real Time Tracker in Excel

In the following article, we will demonstrate how to create a real-time tracker in Excel, with detailed step-by-step procedures. You can also calculate the earnings per day and per year using this template.

Step 1 – Create Primary Outline

First step is to create the structure of the template where we are going to input all the necessary information, which in turn will help us to create a real-time tracker in Excel.

Create a template to capture a person’s daily income over the course of 1 month.
At the top, input the employee’s name, department, responsibilities, id no, and remuneration.

Next, we need to list all the workdays within one month.

Copy the first date to cell B9.
Select cell C6 and right-click on the mouse.
From the context menu, click on Copy.

Select cell B9, right-click on the mouse and from the context menu, click on Values in the Paste Options.

Hold the right mouse button on the corner of cell B9 and then drag it to cell B29.

Release the Flash Fill Handle.
From the context menu, click on Fill Weekdays.

After clicking Fill Weekdays, you will notice that the range of cells B9:B29 is now filled with the weekdays of the entire month.

Step 2 – Input Work Description

Having set up the template, now we are going to input the necessary information into the worksheet.

Insert the work responsibility on each day throughout the year in the range of cells C9:C29.

Input the time each day when the person starts working in the Start Time column.

Document the time when the person ends his workday in the range of cells E9:E29.

Step 3 – Calculate Total Work Hours on Each Day

Now let’s calculate the total number of work hours for each day. We will use the TEXT function to achieve this.

Select cell F9 and enter the following formula:

=TEXT(E9-D9,"h:mm")

This will calculate the Total work hours on the first day of the month.

Drag the Fill Handle to cell F29 to fill the range of cells F9:F29 with the total work hours on each day.

Step 4 – Evaluate Unpaid Time

Now we need to document the time outside of work, which will be considered unpaid.

Fill the range of cells G9:G29 with unpaid work hours for each day.

Step 5 – Calculate Billable Time

From the calculations above, it is pretty evident that the Total working hours alone can’t be used to set the billable time. We need to deduct this time from the Total Hours.

Select cell H9 and enter the following formula:

=F9-G9

This will calculate the Billable time per day.

Drag the Fill Handle to cell H29 to fill the range of cells H9:H29 with the total work hours on each day.

Step 6 – Estimate Gross Pay Based on Billable Time

We can now calculate the gross payment per day, using the HOUR, and MINUTE functions.

Select cell I9 and enter the following formula:

=HOUR(H9)*$H$6+(MINUTE(H9)*$H$6)/60

This will calculate the payment for the first day in cell I9.

Drag the Fill Handle to cell I29 to fill the range of cells I9:I29 with the Gross Pay per day.

Step 7 – Calculate Total Earnings for Entire Month

We can now easily calculate the total earnings per month, using the SUM function.

Select cell I30 and enter the following formula:

=SUM(I9:I29)

This will calculate the total earnings in one month by summing each day’s earnings.

And that is how to create a real-time tracker in Excel.

Download Practice Workbook

Create Real Time Tracker in Excel.xlsx

<< Go Back to Create a Tracker in Excel | Tracker in Excel | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!