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.

Design Primary Summary Layout to Track Comp Time

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

Design Primary Summary Layout to Track Comp Time

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

Design Primary Summary Layout to Track Comp Time

  • 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.

Design Primary Summary Layout to Track Comp Time

  • 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.

Design Primary Summary Layout to Track Comp Time

  • 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.

Design Primary Summary Layout to Track Comp Time

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.

Create Time Tracker Table to Track Comp Time

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

=Summary!C4

  • Press Enter.

Create Time Tracker Table to Track Comp Time

  • 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.

Create Time Tracker Table to Track Comp Time

  • 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.

Create Time Tracker Table to Track Comp Time

  • 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.

Create Time Tracker Table to Track Comp Time

  • 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.

Create Time Tracker Table to Track Comp Time

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

Create Time Tracker Table to Track Comp 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

=IF([@Date]=0," ",CHOOSE( WEEKDAY([@Date]), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))

  • Press Enter.

Create Time Tracker Table to Track Comp Time

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

=IFERROR(IF(COUNT(Comp_Time[@[Start Time]:[End Time]])=4,(IF([@[End Time]]<[@[Start Time]],1,0)+[@[End Time]])-[@[Lunch End Time]]+[@[Lunch Start Time]]-[@[Start Time]],IF(AND(LEN([@[Start Time]])<>0,LEN([@[End Time]])<>0),(IF([@[End Time]]<[@[Start Time]],1,0)+[@[End Time]])-[@[Start Time]],0))*24,0)

  • Similarly, press Enter.

Create Time Tracker Table to Track Comp Time

  • 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.

=SUBTOTAL(109,Comp_Time[Total])

  • Press Enter.

Create Time Tracker Table to Track Comp Time

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

=IFERROR(IF(D9<=E9,D9,E9),"")

  • Again, press Enter.

Create Time Tracker Table to Track Comp Time

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

=IFERROR(E9-F9, "")

  • Press Enter for the last time.

Create Time Tracker Table to Track Comp 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.

Create Time Tracker Table to Track Comp Time

  • 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.

Create Time Tracker Table to Track Comp Time

  • 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.

🔍 Breakdown of the Formula

We are breaking down the formula for cell C12.

We used the nested formula in cell C12 formula to get the weekdays name. The formula is:

=IF([@Date]=0,” “,CHOOSE(WEEKDAY([@Date]),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”))

Now, we are breaking down the formula for cell C12.

👉  WEEKDAY([@Date]: This function returns the sequential value of that weekday. For cell C12, the value is 2.

👉 CHOOSE(WEEKDAY([@Date]),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”): This function will choose the day name. According to our formula, the value 2 represents Monday. As a result, the function returns Monday.

👉 IF([@Date]=0,” “,CHOOSE(WEEKDAY([@Date]),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)): The IF function first check the value of date column. If there is no value available in that column the function will return space (“ “), otherwise, it returns the weekdays name which it will get from the CHOOSE function. For the value of cell B12, the formula returns Monday.

🔍 Breakdown of the Formula

We are breaking down the formula for cell H12.

Similarly, we used another nested formula to get the total working hours of that day in column H.

=IFERROR(IF(COUNT(Comp_Time[@[Start Time]:[End Time]])=4,(IF([@[End Time]]<[@[Start Time]],1,0)+[@[End Time]])-[@[Lunch End Time]]+[@[Lunch Start Time]]-[@[Start Time]],IF(AND(LEN([@[Start Time]])<>0,LEN([@[End Time]])<>0),(IF([@[End Time]]<[@[Start Time]],1,0)+[@[End Time]])-[@[Start Time]],0))*24,0)

Now, we are breaking down the formula for cell H12.

👉 IF([@[End Time]]<[@[Start Time]],1,0): This function returns 0.

👉 LEN([@[Start Time]]: This function returns 12:00 AM.

👉 COUNT(Comp_Time[@[Start Time]:[End Time]]):This function returns 4.

👉 IF(AND(LEN([@[Start Time]])<>0,LEN([@[End Time]])<>0),(IF([@[End Time]]<[@[Start Time]],1,0)+[@[End Time]])-[@[Start Time]],0):This function returns 0.33.

👉 AND(LEN([@[Start Time]])<>0,LEN([@[End Time]])<>0): This function returns TRUE.

👉 IFERROR(IF(COUNT(Comp_Time[@[Start Time]:[End Time]])=4,(IF([@[End Time]]<[@[Start Time]],1,0)+[@[End Time]])-[@[Lunch End Time]]+[@[Lunch Start Time]]-[@[Start Time]],IF(AND(LEN([@[Start Time]])<>0,LEN([@[End Time]])<>0),(IF([@[End Time]]<[@[Start Time]],1,0)+[@[End Time]])-[@[Start Time]],0))*24,0): This formula returns the total working hours for that day. In this cell the value is 6.83.


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.

='Week-1'!D9

  • Press Enter.

Finalize Summary Report to Track Comp Time

  • 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.

=SUM(C9:C13)

  • Press Enter.

Finalize Summary Report to Track Comp Time

  • 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.

=INT(F14/8)&" Days"

  • Press Enter.

Finalize Summary Report to Track Comp Time

  • 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

ROUND(MID(F14/8,FIND(".",F14/8),10)*24,2)&" Hours"

🔍 Breakdown of the Formula

We are breaking down the formula for cell F16.

👉 FIND(“.”,F14/8): This function will search for the location of the decimal point in the result.  In this value, the position of the decimal point is 2. As a result, the function returns 2.

👉 MID(F14/8,FIND(“.”,F14/8),10): This formula will extract the value after the decimal point. We set the formula to extract 9 digits after the decimal. Thus, the function returns .21625.

👉 ROUND(MID(F14/8,FIND(“.”,F14/8),10)*24,2)&” Hours”: Using this formula, we will get the final result. The function will round the value into 2 digits. So, the function will convert the value1.73/8 or 0.22 into the hour. Hence, the function returns 5.2 Hours.

  • Press Enter for the last time.

Finalize Summary Report to Track Comp 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.

Verify with Sample Data

  • Finally, you will get the summary report.

Verify with Sample Data

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


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!

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo