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

Step 1: Design Primary Summary Layout

Select cell B2 and enter the title of this sheet Summary.

Rename the sheet name in the Sheet Name Bar as Summary to easily find the sheet.
In the range of cells B4:B6 and D4:D5, enter the following entities:

Format the neighboring cells of those mentioned cells to ensure the input location of those data.

In the range of cells B8:F8, enter the following table headings:

Denote in range of cells B9:B13, 1-5 for five weeks of that month.
Name cell B14 as Total to show the total of all the columns.
Name cell D16 as Achieved Paid Leave Days.

Insert your company logo.
Select cell E2.
In the Insert tab, click on the drop-down arrow of the Illustration > Picture option.
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.

Step 2: Create a Comp Time Tracker Table

Create a new sheet and rename it Week 1.
Select the entire row 1:7 in the Summary sheet and press ‘Ctrl+C’ to copy everything.

Go to the Week 1 sheet and press ‘Ctrl+V’ to paste.

In cell C5, enter the following formula to extract the value of the employee’s name from the Summary sheet.

=Summary!C4

Press Enter.

Enter the following types of formulas in the range of cells C5:C6 and E4:E5.
Press Enter to store the formula in that cell.

We want to add a weekly summary report to this sheet.
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.

Enter the title Weekly Summary inside the shape.

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

In the range of cells B11:H11, enter the following table headings to create the comp time tracker.

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.

Modify the cell format. Select the range of cells B12:B18, and choose the Short Date format from the Number group.

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

Enter the following formula in cell C12 to get the weekday’s name. 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.

To get the total working hours, enter 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)

Press Enter.

Our weekly tracker is ready. Now, we are going to complete the week’s summary table.

In cell D9, enter the total working hours per your organization’s policy. We entered 35 hours as our policy.

In cell E9, enter 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, enter the following formula in cell F9:

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

Press Enter.

To get the overtime value, enter the following formula in cell G9:

=IFERROR(E9-F9, "")

Press Enter.

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 four additional sheets for the rest of the days of that month.
Input some sample data to check the accuracy of our formulas.

You will get the weekly summary report.

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 weekday 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 weekday 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 a Summary Report

Select cell C9 and enter the following formula into cells to get the value of the total working hours per week.

='Week-1'!D9

Press Enter.

Drag the Fill Handle icon to your right to copy the value-extracting formula up to cell F9.

Insert the same formula to get the summary values for the rest of the weeks.

To get the total, use the SUM function. Enter the following formula into cell C14:

=SUM(C9:C13)

Press Enter.

Drag the Fill Handle icon to your right to get the total of all columns.

To estimate the achievement paid on leave day, enter the following formula in cell E16. To get the complete day value, 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, enter 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.