For the purpose of tracking employees and documenting their workhour in each day, we need a list that can provide this sort of benefit without much hassle. If you are curious to learn how you can create a real-time tracker in Excel, this article may come in handy for you. In this article, we are going to discuss, how you can create a real-time tracker in Excel with elaborate explanations.
Watch Video – Create Real Time Tracker in Excel
How to Create Real Time Tracker in Excel: Step-by-Step Procedure
In the following article, we will demonstrate how you can 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. While making this template, we will use functions like TEXT, HOUR, MINUTE, etc.
Step 1: Create Primary Outline
In the beginning, we need to create the structure of the template where we are going to input all the necessary formations. Which in turn will help us to create the real-time tracker in Excel.
- We will create a template about a persons day by day income over the course of 1 month.
- We input the employee’s name, person’s department, responsibilities, id no, and rate on which he is going to work.
- Next, we are going to list all the workdays within one month.
- To do this, first, we are going to copy the first date to cell B9.
- Select cell C6 and right-click on the mouse.
- Then from the context menu, click on Copy.
- Then select cell B9 and right-click on the mouse and from the context menu, click on Values in the Paste Options.
- Then hold the right mouse button on the corner of cell B9 and then drag it to cell B29.
- After dragging to cell B29, release the Flash Fill Handle.
- Immediately after releasing the Flash Fill, you will see that there is a context menu.
- From the context menu, click on the 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
After setting up the template, now we are going to input the necessary information into the worksheet.
- First, we will insert the work responsibility on each day throughout the year in the range of cells C9:C29.
- Then, we will input the time each day when the person starts working in the Start Time column.
- Next, we will document the time when the person ends his workday in the range of cells E9:E29.
Read More:Â How to Create a Daily Task Sheet in Excel
Step 3: Calculate Total Work Hours on Each Day
After the documentation of the Start Time and the End Time of each day, we will estimate the total no of work hours for each day. We will use the TEXT function to achieve this.
- To do this, select cell F9 and enter the following formula:
=TEXT(E9-D9,"h:mm")
Doing this will calculate the Total work hour on the first day of the month.
- Then drag the Fill Handle to cell F29, doing this will fill the range of cells F9:F29 with the total work hour on each day.
Read More:Â How to Create a Progress Tracker in Excel
Step 4: Evaluate Unpaid Time
After we estimated the total work hour per day, we need to document the time outside of work, which will be considered unpaid.
- We filled out the range of cells G9:G29 with unpaid workhour in each day.
Read More:Â How to Create a Task Tracker in Excel
Step 5: Calculate Billable Time
From the calculations above, it is pretty evident that the only Total working hour can’t be used to set the billable time. We need to didact this time from the Total Hour.
- To do this, select cell H9 and enter the following formula:
=F9-G9
Inserting this formula will estimate the Billable time per day.
- Then drag the Fill Handle to cell H29, doing this will fill the range of cells H9:H29 with the total work hour on each day.
Step 6: Estimate Gross Pay Based on Billable Time
Now as we calculated the billable workhour per day, we can now estimate the gross payment per day, using the HOUR, and MINUTE functions.
- To do this, select the cell I9 and enter the following formula:
=HOUR(H9)*$H$6+(MINUTE(H9)*$H$6)/60
Entering this formula will calculate the payment for the first day in cell I9.
- Next drag the Fill Handle to cell I29.
- Doing this will fill the range of cells I9:I29 with the Gross Pay per day.
Step 7: Calculate Total Earnings for Entire Month
We have already calculated the gross earnings per day. We can easily calculate the total earnings per month, using the SUM function.
- To do this. select the cell I30 and enter the following formula:
=SUM(I9:I29)
Entering this formula will calculate the total earnings in one month by summation of each day’s earnings.
And this is how we create a real-time tracker in Excel in step by step procedure.
Download this practice workbook below.
Conclusion
Here, we created a real-time tracker with detailed step-by-step procedures. You can also calculate the earnings of the employees each day and each month using this template.
For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section.
