Blogger Random - Recent - Specific Label Posts Widget - All in One Post Feed Widget

How to Calculate Overtime Pay in Excel

>> November 15, 2011

Overtime pay is good incentive for employees. It will increase the your organisation's productivity because overtime pay encourages employees to spend more time for organisation's works. Recently, I appointed an employee part time for my Accounting Education. So, I am searching good excel formulas on the net for calculating overtime pay. I found a good excel training channel (name 599CD Computer Training) on the net who provided good formula for calculating overtime pay in excel. I am explaining these formulas stepwise.

 1st Step : To Calculate total Hours of Work in a Week 

Suppose, you have 5 employees who work different time in your organisation. Record their real working hours in a week. On these basis, you can calculate total hours of work in a week. In your case, you can also calculate total hours of work in a month by applying simple sum formula in excel.


2nd Step : To Calculate Normal Hours

By using logical formula, we can calculate normal hours of each employee from total working hours. Following is this formula.


Following is screenshot in which you see, how it has been used in real problem.

After calculating first value, we will drag this value and we will calculate all the normal hours values.

Condition of screenshot question : Those who work 40 hours or less get normal pay. From 40 to 50 hours will receive time-and-a-half, and over 50 hours earns an employee double-time.

3rd Step : To Calculate Overtime in Hours

We will also use logical formula when we will calculate overtime in hours.  Following is this formula.


4th Step : To Calculate Overtime Pay

Now, it will become so easy for you to calculate overtime pay. It is just multiplication of overtime hours and rate of salary. Following video will explain it in detail.

Related : Accounting in Excel Tips 

You might like:

Follow Us


how to calculate overtime May 22, 2012 at 1:19 AM  

Well i am using overtime calculator to get rate about my overtime pay.
Well that's a nice tricks for whom that not have overtime calculator.

Post a Comment

About Accounting Education

An educational site with 2500+ articles, solutions, video-guides and tutorials on all topics related to accounting and finance.

Get Update on Mobile

Type in your mobile phone web browser for free access anytime, from any place.The content is designed specifically for cell phones and mobile devices.

Contact Us

Email :

Phone : +91-8557888436

Send an Email
Phone number and vCard
LinkedIn profile
Follow us on twitter