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.

**Table of Contents**hide

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

`=Summary!C4`

- 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

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

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

`=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**.

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

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

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

`=IFERROR(E9-F9, "")`

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

**🔍 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**.

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

`='Week-1'!D9`

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

`=SUM(C9:C13)`

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

`=INT(F14/8)&" Days"`

- 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

`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 value**1****.73/8** or **0.22** into the hour. Hence, the function returns **5.2 Hours**.

- 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!