Attendance and Overtime Calculation Sheet in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Attendance and Overtime Calculation Sheet in Excel: with Easy Steps

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.

Set Year, Month, Date, and Weekend Data in a Helper Sheet

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

Set Year, Month, Date, and Weekend Data in a Helper Sheet to make Attendance and Overtime Calculation Sheet

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

Set Year, Month, Date, and Weekend Data in a Helper Sheet

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

Set Year, Month, Date, and Weekend Data in a Helper Sheet

 

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)

Set Dates in Main Calculation Sheet to make Attendance and Overtime Calculation Sheet

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

Set Dates in Main Calculation Sheet

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

Set Dates in Main Calculation Sheet to make Attendance and Overtime Calculation Sheet

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

Set Dates in Main Calculation Sheet to make Attendance and Overtime Calculation Sheet

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


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.

Calculate Total Working Hours to make Attendance and Overtime Calculation Sheet

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

Calculate Total Working Hours to make Attendance and Overtime Calculation Sheet

Read More: How to Create Attendance Sheet with Time in and Out in Excel


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.

Set Regular Office Hours and Calculate Overtime 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.

Set Regular Office Hours and Calculate Overtime Hours to make Attendance and Overtime Calculation Sheet


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.

Calculate Overtime Pay


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.

Calculate Total Pay


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)

Make a Weekly Summary


Download Sample Workbook


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. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Employee Attendance Sheet Excel | Excel HR Templates | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

4 Comments
  1. what if employee works for more than 24 hours regularly or 48 hours regularly, can we maintain calculation in single step or if it will be an exception

    • Dear Santosh Kumar,
      Please clarify the question. How can a person do more than 24 hours in 24 hours?
      With Regards.
      Joyanta Mitra

  2. I have an issue with the time, i entered different time and stated on excel different times. I can only do am and not pm. Could you assist?

    • Hello G,

      Thank you for sharing your experience with us! I understand you are facing time issues with your attendance & overtime calculations. You mentioned that you could only get AM time but not PM. This is interesting because Excel has no built-in time format that shows only AM and not PM. All standard time formats like h:mm AM/PM, hh:mm AM/PM, and m:ss AM/PM display both AM and PM for times before and after noon, respectively.

      The reason you are facing this issue could be your regional settings might be set to a locale that uses a 24-hour clock instead of a 12-hour clock with AM/PM indicators. To check your regional format and adjust your settings:
      1. Go to Control Panel.
      2. Click Clock and Region to see your existing time format.
      Another reason could be a Custom Format has been applied to the cells that only show AM and hides PM. Whatever the reasons are, you can make it right using the specific time format. Follow the steps:
      1. Select a blank cell where you want the output.
      2. Enter the following TEXT formula and press Enter.
      =TEXT(A2,"hh:mm:ss AM/PM")
      Using TEXT function
      As a result, the TEXT function converts different time values into text strings. If you want to calculate these times later, then set a custom format:
      1. Select the target cells and press Ctrl + 1.
      2. In the Format Cells dialog, click Time under Category.
      3. Select the desired format code in the Type box and click OK.
      Using Format Cells option
      Thus, you embed the desired time formats.

      Regards,
      Yousuf Shovon

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo