• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

ExcelDataPro

Free Excel Pro Templates

  • Glossary
    • Accounting Glossary
    • HR & Payroll Glossary
    • Tax Glossary
  • Templates
    • Accounting
      • Financial Analysis
      • Financial Statement
    • Educational
    • Taxation
      • Federal Income Tax
      • GST
      • UAE VAT
      • UK VAT
    • Human Resources
      • HR & Payroll
      • HR Metrics
    • School Management
    • Social Media
    • Sales-Marketing
    • Personal Finance
    • Other
      • Health & Fitness
  • Functions
  • FAQ’s
  • Blog

Download Employee Overtime Calculator Excel Template

Download Employee Overtime Calculator Excel Template

Employee Overtime Calculator is a ready-to-use template in Excel, Google Sheets, and OpenOffice Calc that helps you easily calculate overtime.

Table of Contents

  • What is Overtime?
  • Overtime Rules
  • What is Overtime Pay?
  • How To Calculate Overtime?
    • Hourly Employee
    • Salaried Employee
  • Employee Overtime Calculator Template
    • Contents of the Employee Overtime Calculator Template
  • Overtime Exemptions
  • Frequently Asked Questions

What is Overtime?

For an hourly employee, Overtime refers to the extra amount of time an employee spends for works in addition to its regular working hours. In other words, any hours that employee works exceeding their scheduled work-time is overtime.

Overtime is common or even mandatory for employees in industries like construction, IT, Event Management, etc.

Overtime Rules

Normal working differs from industry to industry and even company to company. Some companies have 8 hours and some have 10 hours. Companies determine normal working hours depending on the job type and by the best practices of a profession.

Countries define Labor laws considering the overtime to prevent employees from being forced to work long hours. These laws even contain regulations of compensation for overtime.

Usually, 1.5 times also known as time and half is offered to employees. In some cases, it is offered double of regular hourly pay.

Furthermore, the overtime federal law obligates employers to pay a rate that is greater than the standard wage for regular hours.

It is 40 hours per week in most countries including the U.S. Thus, it is mandatory for the employer to compensate each hour exceeding the normal threshold.

What is Overtime Pay?

Overtime Pay means the amount compensated for hours or days worked more than the maximum limit mentioned in your contract or set by federal/provincial law.

Usually, small companies and factories require managing the employee hours for the payment of overtime. It sometimes becomes a tedious job. Let us discuss how to calculate employee overtime.

How To Calculate Overtime?

There are two types of employees: Hourly Employees and Salaried Employees. The calculation of overtime differs for both categories.

Usually, you can use the formula given below to calculate the overtime pay. But it may differ according to geographical locations or organization./

Hourly Employee

Use the following formula to calculate overtime pay for an hourly employee:

Overtime Pay = Regular pay X 1.5 X number of extra hours

Example

If an employee works 2 hours more than his decided limit, the calculation will be as below:

$20(Regular pay/hour) X 1.5 X 2 hours = $60 Overtime Pay

Salaried Employee

The calculation of overtime for a salaried employee is different. First of all, we need to find his pay per day and then find his pay per hour.

Formula to Calculate Hourly Pay

Hourly Pay For Salaried = Yearly Salary / 2080 hours

OR

Hourly Pay For Salaried = Monthly Salary / 160 hours

Example

If a salaried employee works 2 hours more than his decided limit, where his salary is $2,000. As per the Fair Labor Standard Act (FLSA), An employee whose salary is below $684 per week, is eligible for overtime pay. This limit has been recently increased to $684 from $455.

Thus, the calculation will be as follows:

Hourly Pay = $2,000 / 160 = $12.50

Overtime Pay = $12.50 X 1.5 X 2 = $37.50

Employee Overtime Calculator Template

Overtime Calculator

We have created an Overtime Calculator Excel template with predefined formulas. You can easily and efficiently calculate overtime. Just enter the In and Out time along with the overtime rate and it will automatically prepare the pay sheet for you.

Excel Google Sheets Open Office Calc Apple Numbers

Click here to Download All HR & Payroll Excel Templates for Rs 199.

Additionally, you can download other HR Templates like Salary Sheet, Attendance Register, Gratuity Calculator, etc.

Let us discuss the contents of the template in detail.

Contents of the Employee Overtime Calculator Template

This template consist of 3 sections: Header Section, Summary Section, and Data Input Section.

Header Section

The header section consists of the company name, company logo, and heading of the sheet “Employee Overtime Calculator”.

Overtime Calculator

Summary Section

The summary section consists of the following subheadings:

Overtime Calculator

Employee Name: Name of Employee.

Month: Select the month from the drop-down list.

Overtime Calculator

Click on the link below to learn how to easily create a drop-down list.

How to create a drop-down list in 3 easy steps.

Year: Enter year.

Hours: This cell will display the total number of hours worked by the employee for the whole month.

Rate/Hr: Enter the rate per hour for overtime.

Total OT: Total OT shows the total amount of Overtime earned by the employee.

Data Input Section

The Data Input section consists of the following subheadings:

Overtime Calculator

Date: The date from the 1st of every month till the end is displayed here.

In: Employee In time is entered here.

Out: Employee Out time is entered here.

Overtime: The template automatically calculates the number of hours worked more than the limit.

The formula applied here is =IF(OR(D11=””,F11=””),””,(IF(D11>”12:00″,”00:00″,(“24:00”-D11)+F11))-“08:00”)

We have set the limit to 8 hours. You can change it by changing the last part of the formula.

Rate: Rate per hour of Overtime.

Amount: This column displays the total amount of overtime per day.

In the end, totals of monthly overtime hours and amounts are displayed.

Overtime Calculator

Overtime Exemptions

As per FLSA,

Most employees in the United States be paid at least the federal minimum wage for all hours worked and overtime pay at not less than time and one-half the regular rate of pay for all hours worked over 40 hours in a workweek.

However, FLSA excludes certain jobs from overtime compensation. The Fair Labor Standards Act determines exempt jobs. These include executive, administrative, professional, outside sales employees, and some of the IT sector employees.

Moreover, the employer needs to fulfill many requirements to consider an employee as exempt. Let’s take an example of administrative employees.

To qualify for the administrative employee exemption, all of the following tests must be met:

  • The employee salary should not be less than $684 per week.
  • The employee’s primary duty must be the performance of office or non-manual work directly related to the management or general business operations of the employer or the employer’s customers.
  • The employee’s primary duty includes the exercise of discretion and independent judgment with respect to matters of significance.

To know more about these exemptions click on the link below to download the FLSA explanation:

Exemption for Executive, Administrative, Professional, Computer & Outside Sales Employees Under the Fair Labor Standards Act (FLSA)

We thank our readers for liking, sharing, and following us on different social media platforms.

If you have any queries please share in the comment section below. I will be more than happy to assist you.

Frequently Asked Questions

What are normal hours yearly, monthly, and weekly for a salaried person?

The normal hours are 2080, 160, and 40 respectively for a salaried person.

Can we say no to overtime?

Your employer can ask you to work extra hours even if your contract doesn’t include it. You have a right to say no but if you say no without a good reason it will damage your relationship.

Is straight time overtime legal?

Yes, it is legal but subject to conditions. The employers who offer straight-time overtime must comply with federal wage and hour laws. According to the law, an employer needs to pay all overtime at a rate equal to one and a half times their regular pay for every hour over 40 hours per week to all nonexempt employees in the U.S.

How many hours make you full time?

According to general HR standards, an employee working 30-40 hours or more per week is considered a full-time employee. But a part-time employee is the employee who works less than 130 per month or less 30 hours per week.

« Download Employee Contract Renewal Schedule Excel Template
Download Simple Age Calculator Excel Template »

Filed Under: HR Templates

You are Here: Home / Excel Templates / HR Templates / Download Employee Overtime Calculator Excel Template

About Fahim Lashkaria

I love working in Excel and I share functional excel templates at ExcelDataPro.

Comments

  1. MUHAMMAD SOHAIL NAEEM says

    December 5, 2017 at 9:59 am

    Thank You so much . infact your efforts are priceless. Its made my work so easy and many many thanks.

    Reply
    • Fahim Lashkaria says

      December 5, 2017 at 1:38 pm

      Happy to know that it helped you. We also have many other such templates in our HR Templates category. Apart from that, there are many accountings Templates as well as financial analysis templates which can be helpful to you as well as your colleagues. Please share the links with your friends and colleagues and also on different social media. Thank you for appreciating our efforts. This encourages us to do more.

      Reply
  2. Sanjeev Badami says

    April 11, 2018 at 12:27 pm

    Hi I am a small works shop owner where in we have 8 workers and 1 supervisor
    I have put the attendance machine which gives excel output of the attendance
    The workers are on daily wedge basis and supervisors on monthly wedge system.
    We dont have seperate account hr etc department I only handle the things
    Is it possible to that the data through the attendance machine is directly coupled to the salary calculator
    If not do you have some template where in by feeding the data from the excel the salary is calculated and the salary slip is generated
    We have only basic pay and dont give any allowance except for overtime and bonus for the workers as the works doesn’t fit in to PF bracket we have deduction only against advance
    Thanking you in advance Sanjeev

    Reply
    • Fahim Lashkaria says

      April 12, 2018 at 5:30 am

      Hi Sanjeev. I have understood your point. You will need to customize the sheet merging with salary. It will be a tough job for you. Contact +91 9687858563 for customizing your sheet as per your requirement. Thanks and Regards

      Reply
  3. gin says

    October 29, 2019 at 7:22 am

    how to calculate overtime?

    Reply
    • Fahim Lashkaria says

      November 4, 2019 at 12:23 pm

      Overtime is calculated In time minus out time where the shift hours are predefined to 8 hours.

      Reply
  4. Ajah richard joshua says

    January 6, 2020 at 4:03 am

    I work 13 hours everyday as a clearner insteady of 8 hours meanwhile i have worked there a year and three weeks how much suppose to be my over time money, although my salary is 1200 aed

    Reply
    • Fahim Lashkaria says

      January 7, 2020 at 3:50 pm

      Insert your hours of work in-time, out-time and overtime rate for the complete time period in the calculator to know the exact amount of your overtime.

      Reply
  5. Aida Ishak says

    January 29, 2020 at 10:29 am

    Hi,

    Can you help me to calculate overtime in excel with two rate. The example as below:

    start End Rate
    22:00 6:00 1.25
    6:00 8:00 1.125
    17:00 22:00 1.125

    In Out Rate 1.125 Rate 1.25 Total ($8.89/hours)
    5.00am 8.00am 2 hours 1 hours $31.11

    How i want to excel automatic calculate hours in column rate.

    Thanks for your help.

    Reply
    • Fahim Lashkaria says

      January 29, 2020 at 3:57 pm

      I cannot understand your query, Aida. Can you please simplify it.

      Reply
  6. PRASANNA KUMAR says

    February 18, 2020 at 1:11 pm

    DEAR SIR
    KINDLY TEL ME THE WHAT IS THE CALCULATION OF EPF & ESI ON OVERTIME PAYMENTS FOR SALARY AND WAGES.
    THANKING YOU

    Reply
    • Fahim Lashkaria says

      February 19, 2020 at 12:55 pm

      EPF and ESI is calculated on your salary and not overtime.

      Reply
      • PRASANNA KUMAR says

        February 19, 2020 at 1:13 pm

        THANK YOU, SIR BUT SOME PERSONS SAYING ESI CALCULATED ON OVERTIME, KINDLY CLEAR MY CONFUSION.
        THANKING YOU.

        Reply
        • Fahim Lashkaria says

          February 19, 2020 at 2:12 pm

          Employees’ State Insurance is a self-financing social security and health insurance scheme for Indian workers. For all employees earning Rs. 15000 or less per month as wages, the employer contributes 4.75 % and employee contributes 1.75 %, i.e., total share 6.5 %. ESIC is calculated on gross salary (excluding washing allowance). Employees drawing gross salary above Rs. 15000 will not be applicable under ESIC.

          Reply
  7. MOHD IBRAHIM says

    May 21, 2020 at 3:25 pm

    HOW TO CHANGE PREDEFINED 8 HOUR SHIFT TO 10 HOUR SHIFT.

    Reply
    • Fahim Lashkaria says

      May 21, 2020 at 6:42 pm

      Change the formula in overtime column to this =IF(OR(D11=” ”, F11=” ”),” ”,(IF(D11>”12:00″,”00:00″,(“24:00”-D11)+F11))-“10:00”)

      Reply
  8. Biodun Samuel Adepetu says

    February 5, 2021 at 2:56 am

    Tnx.
    Great job

    Reply
    • Fahim Lashkaria says

      February 5, 2021 at 7:05 pm

      Thanks for the Appreciation, Biodun

      Reply
  9. Fa says

    February 12, 2021 at 9:09 pm

    In: 13:54
    Out: 21:35

    It less than 8 hours. but when I use the given formula, I got 23:44 as overtime.
    how to overcome this?

    Thx.

    Reply
    • admin says

      February 13, 2021 at 1:27 pm

      Keep the minutes as same and it should solve the problem. The overtime is coming because clock calculation is in rotation basis.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

HR Templates

  • Salary Sheet Excel Template
  • Daily Work Schedule Excel Template
  • Weekly Timesheet Excel Template
  • Corporate Salary Slip Excel Template
  • Employee Attendance Sheet Excel Template
  • Employee Performance Evaluation Excel Template
  • Employee Provident Fund Calculator Excel Template
  • Job Candidate Tracker Excel Template
  • Employee Resignation Schedule Excel Template
  • Employee TA Reimbursement Excel Template
  • Employee Contract Renewal Schedule Excel Template
  • Employee Overtime Calculator Excel Template
  • Simple Age Calculator Excel Template
  • Employee Mileage Log Excel Template
  • Multiple Employees Weekly Timesheet Excel Template
  • 9 Ready-To-Use Salary Slip Excel Templates
  • Salary Sheet With Attendance Register In Single Excel Template
  • Weekly Timesheet With Payslip Excel Template
  • Salary Breakup Report Excel Template
  • Gratuity Calculator (India) Excel Template
  • Taxable Gratuity Calculator Excel Template
  • Employee Training Log Excel Template
  • Employee Vacation Tracker Excel Template
  • Salary Arrears Calculator Excel Template
  • Resume/Cover Letter Excel Template
  • Professional Resignation Letter Excel Template
  • Employee Termination Letter Excel Template
  • Employee Promotion Letter Excel Template
  • Salary Certificate Format Excel Template
  • Absconding Letter Excel Template
  • Employee Disciplinary Memo Excel Template
  • Casual Leave Application Excel Template
  • Employee Transfer Letter Excel Template
  • Employee Show Cause Notice Excel Template
  • Promotion Request Letter Excel Template
  • Transfer Request Letter Excel Template
  • Job Rejection Letter Excel Template
  • Promotion Rejection Letter Excel Template
  • Transfer Refusal Letter Excel Template
  • Transfer Rejection Letter Excel Template
  • Employee Welcome Letter Excel Template
  • Employee Thank You Letter Excel Template
  • Layoff Notice Letter Excel Template
  • Employment Verification Letter Excel Template
  • Job Offer Letter Excel Template
  • Accommodation Request Letter Excel Template
  • Loan Application Letter Excel Template
  • Workplace Harassment Complaint Letter Excel Template
  • Internship Request Letter Excel Template
  • Additional Facility Request Letter Excel Template
  • Salary Increment Request Letter Excel Template
  • Employee Of The Month Certificate Excel Template
  • Home
  • Contact
  • Privacy
  • Terms

© ExcelDataPro 2021. Content Licensed Under Creative Commons with Attribution Required

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Non-necessary

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.

SAVE & ACCEPT