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.

=sum(number1,number2........)

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.

=IF(logical_test;value_if_true;value_if_false)


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.

=IF(logical_test;value_if_true;value_if_false)





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


1 comments:

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 svtuition.org 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


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