You are in the right place if you have questions about how to keep records of **attendance** and calculate overtime hours in Excel based on employees’ “In Time” and “Out Time”. In this article, you will learn 7 easy steps to make attendance and overtime calculation sheet in Excel.

**Table of Contents**hide

## Download Sample Workbook

## Steps to Make Attendance and Overtime Calculation Sheet in Excel

Suppose, You are a manager, and Robert De Niro is an employee of your company who works in the IT section. You need to make De Niro’s attendance and overtime calculation sheet in Excel. The following steps will help you to do this.

### Step 1: Set Year, Month, Date, and Weekend Data in a Helper Sheet

- Before making the drop-down list, we need to make a dataset containing Years, Months, Days, and Weekends in a separate worksheet (named
**Data**) as shown in the following image.

- Now, select cell
**B8,**then go to the**Data**tab >>**Data Tools**>>**Data Validation.**A**Data Validation**Window will pop up.

- At the Allow box, choose
**List**>> in the source box select the**Year**column from the sheet named**Data (A2:A11).**Next, click on**OK.**

- Now, here you will see the drop list of years. In a similar manner, you can create drop-down lists for
**Month, Date,**and**Weekend.**Every time you have to choose their respective data from the source sheet called**Data.**

**Read More: ****How to Create Monthly Attendance Sheet in Excel with Formula**

### Step 2: Set Dates in Main Calculation Sheet

- Now, to work on the main table, we need to set the
**Day.**To do so, first, you need to type the following formula into cell**B11.**Press**ENTER.**

`=DATE($B$8,MATCH($C$8,Data!$B$2:$B$13,0),Weekly!$D$8)`

- For the rest of the cells, type
**=B11+1**into cell B12, press**ENTER.**Then, by dragging the**Fill Handle**down, you will complete the**Day**column with the name of the weekdays.

- In a similar manner, you can set the
**Date**column by applying the following formula to cell**C11,**then press**ENTER.**

`=DATE($B$8,MATCH($C$8,Data!$B$2:$B$13,0),Weekly!$D$8)`

- Again, into cell
**C12,**just type**=C11+1**to get the consecutive dates. Then, you need to drag down the formula to the rest of the cells.

**Note:**

You can specify the weekend. Drop-down menus offer a variety of options. You can choose no weekend, a 1-day weekend (Mon, Tue…), or a 2-day weekend (Fri & Sat, Sat & Sun..).

**Read More: ****Attendance Sheet in Excel with Formula for Half Day (3 Examples)**

### Step 3: Calculate Total Working Hours

- Before going further calculation, we need to put some inputs such as the data of Entrance and Exit.
- Inbuilt checks ensure
**‘Entrance at’**isn’t later than**‘Exit at’**. In such a scenario, the user would not be able to enter the time.

- Our first step will be to calculate the total working hours and enter them into cell
**F11**using the following formula. Then dragging down the Fill handle, you will get the total working hours for the rest of the weekdays.

`=(E11-D11)*24`

In this case, we subtract the end time from the start time and multiply it by 24 to convert it to decimal hours.

**Read More: ****How to Make Time Attendance Sheet in Excel (2 Easy Ways)**

### Step 4: Set Regular Office Hours and Calculate Overtime Hours

- To set regular office hours just type the following formula into cell
**G11.**

`=MIN(8,F11)`

Here the **MIN function** results in the smaller one between 8 and total hours.

- Again, just type the following formula into cell
**H11**to get the overtime hours.

`=(F11-G11)`

There’s not a lot to see. To calculate overtime, we subtract regular hours from total hours. If total time equals regular time, the result will be zero.

### Step 5: Calculate Overtime Pay

- By using this formula, we can calculate overtime pay.

`=H11*$H$8`

Here **H8** contains the value of overtime pay hourly. If we multiply this value with overtime hours, it will return the overtime pay.

**Read More: ****Attendance Sheet with Salary in Excel Format (with Easy Steps)**

### Step 6: Calculate Total Pay

- In Cell
**J11,**we use the following formula to get the Total Pay.

`=(G11*$G$8)+I11`

To break down the formula, **(G11*$G$8)** calculates regular pay, while **I11** denotes overtime pay. Our total pay is the sum of these two.

### Step 7: Make a Weekly Summary

- Finally, to calculate the weekly summary, we can use the
**SUM function.**For example, if we want to get total pay for the whole week, just type the following formula into cell**J18.**

`=SUM(J11:J17)`

## Conclusion

In this tutorial, I discussed 7 easy steps to make attendance and the calculation sheet in Excel. I hope you found this article helpful. You can visit our website** ExcelDemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**How to Create Employee Attendance Sheet with Time in Excel****How to Create Student Attendance Sheet in Excel with Formula****How to Make Daily Attendance Sheet in Excel (2 Effective Ways)****Attendance and Overtime Calculation Sheet in Excel****QR Code Attendance Tracking with Excel (with Easy Steps)**