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!

2 Comments

Sarah MacDermott Apr 11, 2025 at 7:48 PM

Hello, I have just been watching your Create Real Time Tracker in Excel which was very helpful and i appreciate that you have made this available. My challenge is that this is the CLOSEST solution i have come across to assist me but it still doesnt fully meet my needs and my excel skills are VERY basic so I am struggling to work out how to adapt this template and i was wondering if you could advise me? I am a support coordinator working with people with disability to help them to manage their overall government disability services funding. The client is given a set support coordination budget for a year (eg: $2403.36 for 24hrs of support).Basically what i need to do is to Start off each billing period with the currently available set budget. I then provide 3 types of services for my clients (face-to-face, non-face-to-face and travel costs) within any billing period. I need to start each billing period with the amount of funding that is available after deducting the funding used in the last billing period. In order to keep track of my budget and avoid running out of funding over the whole year, i need to be able to have a real time tracker , so that, when i provide a service eg: 30 minutes of face-to-face support, i can then add the date and type of service provided to the table and have a formula that works out how much the service cost (eg: including start and end time of the service, total time spent, hourly rate and total cost of the service), then deduct this from the previous balance, to show me the current real time available balance after each individual service is provided. Is this possible to do? I am happy to discuss cost if you need to charge me for your assistance. Thank you for considering my request. Regards, Sarah
- Reply
  Shamima Sultana Apr 11, 2025 at 8:22 PM
  
  Hi Sarah MacDermott,
  
  Thank you so much for your kind words! I’m really glad the tracker was helpful to you so far.
  
  What you’re describing is absolutely possible in Excel, and it’s a very meaningful use case. With a few adjustments, we can definitely adapt the template to suit your specific needs.
  
  From what you explained, you need:
  1. A way to input multiple service types (face-to-face, non-face-to-face, travel),
  2. A system to log each service with time and date,
  3. A formula to calculate the cost per service (based on time and rate),
  4. And most importantly, a running balance that carries over across billing periods and updates in real time.
  
  This can be done using a combination of formulas like IF, SUMIFS, INDEX, MATCH, and a well-organized table layout.
  
  I’d be happy to walk you through the steps or help you create a customized version of the tracker. Since you mentioned your Excel skills are basic, I can keep the instructions simple and visual.
  
  Regards
  ExcelDemy