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
From India, Mumbai
Acknowledge(4)
Amend(0)
5Please 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
From India, Mumbai
Acknowledge(4)Amend(0)
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
From India, Mumbai
Acknowledge(1)Amend(0)
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
From India, Mumbai
Acknowledge(1)Amend(0)
Hi use this formula in excel sheet =SUM(D5-C5+IF(C5>D5,1))., change rows in time mode Prema
From India, Madras
Acknowledge(2)Amend(0)
From India, Madras
Acknowledge(2)Amend(0)
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.
From India, Hyderabad
Acknowledge(2)Amend(0)
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.
From India, Hyderabad
Acknowledge(2)Amend(0)
Hi Shraddha, I am attaching one file which may be meet to your requirement, just put o.t. & change in Basic Salary, it will calculate Over time wages. Thanks & Regards Manoj Yadav
From India, Delhi
Acknowledge(5)
Amend(0)
From India, Delhi
Acknowledge(5)Amend(0)
Can Any body , Explain the formula of Over Time Properly and complete with some Example. Regards Smrins
From India, Hyderabad
Acknowledge(0)Amend(0)
From India, Hyderabad
Acknowledge(0)Amend(0)
Dear Yadav, I think your OT calculation formula is worng.Because The OT calculation could be on gross salary and we should pay double amount of daily wage.
From India, Hyderabad
Acknowledge(2)
Amend(0)
From India, Hyderabad
Acknowledge(2)Amend(0)
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
From India, Mumbai
Acknowledge(1)Amend(0)
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
From India, Mumbai
Acknowledge(1)Amend(0)
Dear Shradhha, OT is calculated as : (Basic + Sp. All OR D. A.) / No. of days in a month / 8 hrs * OT hrs * 2 Regards Reshma
From India, Mumbai
Acknowledge(2)Amend(0)
From India, Mumbai
Acknowledge(2)Amend(0)
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
From India, Delhi
Acknowledge(1)Amend(0)
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
From India, Delhi
Acknowledge(1)Amend(0)
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
From India, Bangalore
Acknowledge(1)Amend(0)
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
From India, Bangalore
Acknowledge(1)Amend(0)
DA is dearness Allowance depends upon the %age of basic. if Basic is 20000 and da is 50% then da is 20000*50/100 = 10000
From India, Chandigarh
Acknowledge(0)Amend(0)
From India, Chandigarh
Acknowledge(0)Amend(0)
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
From India, Bangalore
Acknowledge(1)Amend(0)
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
From India, Bangalore
Acknowledge(1)Amend(0)
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.
From Kenya, Nairobi
Acknowledge(0)Amend(0)
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.
From Kenya, Nairobi
Acknowledge(0)Amend(0)
Shraddha......... my OT calculator see if this can help you? waiting for your reply..........................................................
From India, Anand
Acknowledge(1)
Amend(0)
From India, Anand
Acknowledge(1)Amend(0)
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
From India, Mumbai
Acknowledge(0)Amend(0)
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
From India, Mumbai
Acknowledge(0)Amend(0)
Dear Sir, Kindly tell me why should we deduct half an hour after 5 hours of over time.
From India, Mumbai
Acknowledge(0)Amend(0)
From India, Mumbai
Acknowledge(0)Amend(0)
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.
From India, Hyderabad
Acknowledge(0)Amend(0)
- 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.
From India, Hyderabad
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Excel Formulas.xls
Excel Formulas.ppt