How Do I Calculate Overtime Hours in Excel for Late Night Shifts?

Hello,

Please help me with how to calculate the overtime hours in an Excel sheet. I want the number of hours calculated, like if work starts at 9:00 AM and continues until 12:00 AM. How should it be calculated? Are there any specific formulas in Excel that can help with this calculation? Please assist me. Any reply will be greatly appreciated.

Regards,
Shraddha

Hi Shraddha,

Greetings!

As per my knowledge, OT is usually calculated as double. Let's say: 3 hrs x 2 = 6 hrs. So, hopefully, no such formulae are required. Any seniors can guide, please...!!!

Regards,
Ram
ramkpoddar82@gmail.com

Hi,

Please use this formula in an Excel sheet: =SUM(D5-C5+IF(C5>D5,1)), and change the rows to time mode.

Prema

Hi Shraddha!

It is very simple to calculate the OT. As per labor laws, it must be at double the normal rate. As you asked, from 9 AM to 12 Noon, the number of hours worked is 3, for which extra work has been done by the employee. Coming to the calculation of the amount, if the employee's Basic plus DA is, let's say, 'X', then the OT payable will be (X/120)*3 OR (X/240)*3*2. In other words, if his basic+DA is Rs 6000, then the OT payable per hour is = (6000/120)*3 = 150. Please note that out of all components of salary, only the Basic + Fixed DA + Variable DA, if any, have to be considered for calculating the overtime. In lieu of overtime, which has to be double the wage rate, it is a practice in some organizations to pay at a single rate as far as the monetary benefit and compensatory time off are concerned.

With Regards,
Kashinatham Chitturi.

Hi Shraddha,

I am attaching one file which may meet your requirements. Just put o.t. & change in Basic Salary, it will calculate Overtime wages.

Thanks & Regards,

Manoj Yadav

Can anybody explain the formula for overtime properly and provide some examples?

Regards,

Smrins

Dear Yadav,

I think your OT calculation formula is wrong. The OT calculation could be based on the gross salary, and we should pay double the amount of the daily wage.

Hi,

The OT Formula is = ROUND((BASIC + DA) / 30 / 8 * 2). The Overtime is normally double on basic and DA. It should be divided by the total days of the month and working hours, i.e., 8 hours, and then multiplied by 2.

Regards,
Meera

Dear Shraddha,

OT is calculated as: (Basic + Sp. Allowance OR D. A.) / Number of days in a month / 8 hours * OT hours * 2.

Regards,
Reshma

Dear,

The DA is "DEARNESS ALLOWANCE" paid periodically to the employees to enable the neutralization of the cost of living. It is substituted by IDA (INDUSTRIAL DEARNESS ALLOWANCE) or COLA (COST OF LIVING ALLOWANCE) for the industrial workers. It forms a major part of the salary and is considered a component of wages for the calculation of PF, GRATUITY, LEAVE ENCASHMENT, etc.

R.K. SINGH

Hi Shraddha,

This was discussed several times on this site. Actually, you have to consider ordinary wages, not basic + DA as per the rule. The number of days for calculation is 26, not 30. So, in Excel, you can use the formula as mentioned below:

=ROUND(((ordinary monthly wage/26/8) * no of OT hours * 2), 0)

Ordinary monthly wage refers to the monthly salary. If the employee receives extraordinary wages like travel, special allowance, performance incentives, telephone allowance, or any other extra benefits apart from the monthly wage/salary, they can be excluded while calculating.

Regarding ordinary wages, the discussion was incomplete in the previous post. I request senior members to provide suggestions on this.

Regards,
Ramnath

Dear user,

Dearness Allowance (DA) depends on the percentage of the basic salary. For example, if the basic salary is $20,000 and DA is 50%, then DA would be calculated as follows:

$20,000 * 50/100 = $10,000

I hope this helps. Let me know if you have any further questions.

Best regards

Dear seniors,

Can anybody send me the details of components for overtime calculation as per the Factories Act?

For example, components like:

BASIC + DA + HRA + CA + SP = Total
Rs.2725/- Rs.200/- Rs.200/- Rs.400/- Rs.3525/-

How do we calculate per hour?

Please guide me without delay.

Thanks & Regards,
Avinash Kumar.K

Hi,

I really appreciate the Excel spreadsheet you shared, but could you please explain to me how it works? For me, overtime is paid at 1.5 times the normal hourly rate. Overtime is anything more than 52 hours per week. What should I input in the columns for the days of the month? The overtime hours? What about under Basic, is that the monthly salary or hourly salary?

Thank you.

My OT calculator - see if this can help you? Waiting for your reply...

Thank you.

Overtime Calculation in Excel

Per day salary (a) = basic / number of days in the month. In case of double overtime: overtime rate per hour = a/4. In case of single overtime: overtime rate per hour = a/8.

Now, a simple thing: your question was if a worker works from 9 am to 12 am. Just keep in mind that overtime for 12 hours of work, i.e., 9 am to 9 pm = 3. So, 9 am to 10 pm = 4, and 9 am to 11 pm = 5. After 5 hours of overtime, you have to deduct 1/2 hour. So, 9 am to 12 am = 5.5 overtime hours.

Regards,
Kalpit Mahendra Mhatre
Larsen and Toubro Ltd.
APU-ONGC Project
HCP Division

Dear Sir, Kindly tell me, why should we deduct half an hour after 5 hours of overtime?

Can anybody help with this problem? Generally, my company separates morning overtime and evening overtime.
- Work start time: 08:30
- Work sign-in time: 09:45
- Work sign-out time: 18:30
- Work end time: 19:30

For all the above calculations, I need a formula. In this situation, 8 hours should be removed daily, and any extra time should be calculated as overtime for employees. How can I calculate this? If anybody knows, please help me in this regard.