How to Track Comp Time in Excel (with Quick Steps)

Compensatory time tracking is a vital element for any organization. Using this report, a company can provide their staff paid on leave in exchange for their overtime. In this article, we will demonstrate how to track comp time in Excel or how to create the comp tracker. If you are curious, download the free template and follow us.

Download Practice Workbook

Download this free workbook for practice while you are reading this article.

What Is Compensatory Time Tracker?

Compensatory time means providing a paid leave in exchange for the overtime of an employee of any institution. Many organizations provide such facilities to their staff to encourage them for overtime according to their ability. In such a situation, both get benefits from such type of facility.

Step-by-Step Procedure to Track Comp Time in Excel

In this context, first, we will demonstrate to you the step-by-step procedure of how to create a comp time tracker, and then we will show you how to use it.

Step 1: Design Primary Summary Layout

In the first step, we are going to create our primary summary report layout.

• First of all, select cell B2 and write down the title of this sheet Summary.

• Besides it, rename the sheet name in the Sheet Name Bar as Summary so that you can easily find the sheet.
• Then, in the range of cells B4:B6 and D4:D5, write down the following entities.

• After that, format the neighboring cells of those mentioned cells to ensure the input location of those data.

• Now, in the range of cells B8:F8, write down the following table headings.

• Denote in range of cells B9:B13, 1-5 for five weeks of that month.
• Then, entitled cell B14 as Total to show the total of all the columns.
• At last, to show the achieved days, titled cell D16 as Achieved Paid Leave Days.

• Finally, insert your company logo.
• For that, select cell E2.
• In the Insert tab, click on the drop-down arrow of the Illustration > Picture option.
• After that, choose the This Device option.

• As a result, the Insert Picture dialog box will appear.
• Choose your institution’s logo and click Insert. In our case, we insert our website’s logo.

• Our preliminary summary design is completed.

Thus, we can say that we have finished the first task to track comp time in Excel.

Step 2: Create Comp Time Tracker Table

Now, we have to design our mainly weekly comp tracker file. The steps of its creation are shown below:

• First, create a new sheet and rename it, Week-1.
• Now, in the Summary sheet, select the entire row 1:7 and press ‘Ctrl+C’ to copy everything.

• After that, go to the Week-1 sheet and press ‘Ctrl+V’ to paste.

• Next, in cell C5, write down the following formula to extract the value of the employee’s name from the Summary sheet.

• Press Enter.

• Similarly, write the following types of formulas in the range of cells C5:C6 and E4:E5.
• After inserting every formula press Enter to store the formula in that cell.

• Now, we want to add a weekly summary report to this sheet.
• For that, in the Insert tab, click the drop-down arrow of the Illustration > Shapes option. Choose a shape according to your desire. In our case, we choose the Arrow: Pentagon shape.

• Then, write down the title Weekly Summary inside the shape.

• Afterward, in the range of cells D8:G8, write down the following summary table headings.
• Allocate a cell below every heading to show their results.

• Now, in the range of cells B11:H11, write down the following table headings to create the comp time tracker.

• Then, select the range of cell B11:H18 and press ‘Ctrl+T’ to convert the dataset into a table.
• A small window called Create Table will appear.
• Check the option My table has headers and click OK.

• After that, we have to modify the cell format. Select the range of cells B12:B18 and from the Number group, choose the Short Date format.

• Similarly, for the range of cells D12:G18, modify the cell format from General to Time.

• Then, to get the weekdays name, write down the following formula into cell C12. For that, we will use the IF, CHOOSE, and WEEKDAY functions

• Press Enter.

• At last, to get the total working hours, write down the following formula into cell H12 using IFERROR, IF, COUNT, AND, and LEN functions.

• Similarly, press Enter.

• Our weekly tracker is ready.
• Now, we are to going to complete the week summary table.
• In cell D9, write down total working hours according to your organization’s policy. We write 35 hours as our policy.

• Then, in cell E9, write down the following formula to get the total worked hours using the SUBTOTAL function.

• Press Enter.

• To get the value of regular working hours, write down the following formula in cell F9.

• Again, press Enter.

• At last, to get the overtime value, write down the following formula in cell G9.

• Press Enter for the last time.

• Finally, select cell B2 and change the sheet title Summary to Compensatory Time Tracker (Week-1).
• Our weekly comp time tracking sheet for the first week is completed.

• Similarly, create four additional sheets for the rest of the days of that month.
• Now, input some sample data to check the accuracy of our formulas.

• You will get the weekly summary report in an instant.

Finally, we can say that we have completed the second task to track comp time in Excel.

Read More: Excel Timesheet Formula with Lunch Break (3 Examples)

Step 3: Create Summary Report

In the following step, we are going to complete the summary report for a month.

• At first, select cell C9 and write down the following formula into cells to get the value of the total working hours per week.

• Press Enter.

• Then, drag the Fill Handle icon to your right to copy the value extracting formula up to cell F9.

• Similarly, insert the same type of formula to get the summary values for the rest of the weeks.

• Now, to get the total, we will use the SUM function. Write down the following formula into cell C14.

• Press Enter.

• Similarly, drag the Fill Handle icon to your right to get the total of all columns.

• Finally, to estimate the achieve paid on leave day, write down the following formula into cell E16. To get the complete day value, we will use the INT function.

• Press Enter.

• You may notice in the manual calculation that our formula deducts the value after the decimal. To get that value in hour format, write down the following formula into cell F16 using the ROUND, MID, and FIND functions

• Press Enter for the last time.

• Our summary report is completed.

In the end, we can say that we have completed the final step to track comp time in Excel.

Step 4: Verify Comp Time Tracker with Data

Here, we will input some sample data and check the accuracy of our comp tracker.

• First of all, input the employee details in the Summary sheet.

• After that, input some sample data in the sheet named Week-2.

• Finally, you will get the summary report.

Thus, we can say that all of our formulas worked perfectly and we are able to track comp time in Excel.

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

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to track comp time in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!

Related Articles

• How to Track Time Spent on Projects in Excel (5 Useful Methods)
• Timesheet Formula in Excel (5 Examples)
• How to Track Employee Vacation Time in Excel (with Easy Steps)