Executive Human Resource At Emcure Pharmaceuticals
Hr ( Generalist)
Reshma Hadshi
Hr - Generalist
Samuel R
Digital Marketing
Sr.admin Assistant
Manpower Planning
Ms Office
+14 Others

Please help me how to calclaute the overtime excel sheet. iwant the no. of hours calculated like if work starts at 9:00 Am. and if he work till 12:00 am. hw it will be calculated.. Any specific formulas in excel sheet.pls help me out.. will be needful..
23rd November 2009 From India, Mumbai
Hi Shraddha,
As per my knowledge OT usually calculated as double, let say:
3 hrs x 2 = 6 hrs.
So hopefully no such formulae required. Any seniors can guide please.....!!!

23rd November 2009 From India, Mumbai
Hi use this formula in excel sheet =SUM(D5-C5+IF(C5>D5,1))., change rows in time mode Prema
23rd November 2009 From India, Madras
Hi Shraddha!
It is very simple to calculate the OT. As per labour laws, it must be @ double the normal rate. As you asked, from 9AM to 12Noon, the number of hours are 3 for which extra work has been done by the employee. Coming to calculation of amount, If the employee's Basic plus DA is supposing 'X' , then the OT payable will be (X/120)*3 OR (X/240)*3*2. In otherwords, if his basic+DA is Rs 6000, then the OT payable per hour is =(6000/120)*3=150. Pls note that out of all components of salary, only the Basic+Fixed DA+Variable DA if any only have to be considered for calculating the Over time. In lieu of Overtime which has to be double the wage rate, it is a practice in some of the organisations to pay at single rate as far as monetary benefit and plus on compensatory off also.
with Regards.
Kashinatham Chitturi.
24th November 2009 From India, Hyderabad
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
24th November 2009 From India, Delhi

Attached Files
Membership is required for download. Create An Account First
File Type: xls OverTime.xls (35.5 KB, 25256 views)

Can Any body , Explain the formula of Over Time Properly and complete with some Example. Regards Smrins
24th November 2009 From India, Hyderabad
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.
24th November 2009 From India, Hyderabad
The Ot Formula is =ROUND((BASIC+DA)/30/8*2)
The Over Time is normaly double on basic and DA, it should be devided by total days of month and working hours i.e. 8 hrs and then multiply into 2.
24th November 2009 From India, Mumbai
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
25th November 2009 From India, Mumbai
The DA is "DEARNESS ALLOWANCE" paid periodically to the employees to enabling the neutrilize 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 salary and considered component of wages for calculation of PF, GRATUITY, LEAVE ENCASHMENT etc.
25th November 2009 From India, Delhi
Hi Shraddha,
this was several times discused in this site.
actualy you have to take on ordinary wages, but not basic + DA as per rule & no of days for calculation is 26 & not 30.
so in excel u can put d formula as men below:
=round(((ordinary monthly wage/26/8)*no of OT hours *2),.0)
odinary monthly wage means monthly salary & if the employee recieve extra -ordinary wages like travel, spl allow, performance incentive, tel allow and other extra benefit appart from monthly wage/salary can be excluded while calculating.
and reg ordinary wage the discusion was incomplete in the previous posting. so i reguest seniour can suggest on this.
25th November 2009 From India, Bangalore
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
30th November 2009 From India, Chandigarh
Please find new version of Salary Master with new Feactures..

Totally Usefriendly program.
You can enter your Company name,Address,Department,Designations
You can set Categoeries of Fixed earning-Other Earning-Fixed deduction-Other deduction in Salary Slip.
You can set limit of ESI,P.F,Income Tax Slab for Man and Woman,Surchage setting,Professional Tax.

Program is Designed on Two mode
(1) In Auto mode salary slip will be generated as per Your predefined data given in "Company Detail Sheet"
(2) In Manual mode Salary Slip will be generated as per Manual detail given by You.It will not concern with the data given by you in "Company detail sheet"
Ex: We need to generate salary for Regular Employee as per company Policy for that You may put this employees on Auto mode and it will generate Salary slip as per your predefined data (If Basic is 25% of Gross than auto calculate it)But Some Employees are Temperaroy or We don't Follow Rules as per Regular employee so that This Type of Employee Put on Manual mode and enter your Data manually(here You have manually enter Basic and other details).

Software Contains:

1-Attendance Register.
2-Payroll Register.
3-Generate Salary Slip.
4-Track Employees' Leave history.
5-Track Income Tax of All Employee.
6-Print out Leave History of Employee.
7-Overtime Monitor.
8-Master Data sheet of All Employees.
9-Leave Calculation sheet.
10-ESI Calculation(Set Your Requirements)
11-P.F Calculation
(Here there are Two Options one is if Basic<=6500 than Basic*12% otherwise 6500*12%
Second is 12% of Basic)
12-Pension Scheme (not more than 541 rs submission)
13-TDS Calculation
14-Annual Salary statement of Employee and Employer
15-Employer Monthly Salary statement
16-Income Tax Calculations
17-Generation of Form No-6 (For ESIC)
18-Generation of Form no-3A (PF)
19-Generation of Form no-6A(PF)

Limitation: You have monitor Leave of employee each month when It goes minius Red indication is glow on that cell so You have to Put it on LWP otherwise minus leave will effect on salary sllip generation.
Requied Attention: Please be vigilant about Auto and Manual mode and P.F Selection.

If You find this is useful for You than Don't miss to deop Thanks...

Download: <link no longer exists - removed>
Please chase the steps as per the given sequence.

First open "Company Detail Sheet"
1-Open "Company Detail Sheet"
2-Change Company name and Address.
3-Change Salary discriptions: By default I gave some categories for Fixed earning/Reimberancement/Other deductions. Change the Names of This Categories as per your reqirements.
4- As you enter detail mentions in step 3 name of the categories will appear in auto generation salary just below the Salary discription.
Enter percentage as per your company requirements to generate salary of all employees(By default I gave Basic= 25% of gross .You can change 25% and enter your %)
5-Enter % of ESI from Employee and Employer and For P.F Choose your Options.
6-For Professional tax Slab enter your criteria.
7- Enter Name of Departments/Designation of Employees of your Orgenizations.
8-Enter Tax Slab for man and Woman.

second open "Master Data Sheet"

1-Enter Employee name-gender-employee code.
2-select his/her designation-department
3-Enter Pan no-Bank A/C No-Bank name-P.F no-ESI No-DOB-DOJ-Location
4-Enter Employees Gross salary/Month
5-Here after If You choose Auto mode than Salary slip will be generated autonatically as per (%wise detail) data given by you in "Company detail sheet"(nomally for Regular Employees put Auto mode).But if You don't want to follow the rules enter by You in "Company detail sheet" put manual mode here You can Enter his/her Basic-Hra and all detail of salary manually.
6- Enter Amount of OT/Hr if Employee do OT
7- Enter leave detail,Previous Year Leave-This Year Leaves.
8-Enter Rentpaid by Employee for exemption under section 10&17
9-Enter Amount for section 80c and 80D for U/S VIA deduction.

Thrid open "Attendance sheet according to perticular Month"

1- Enter Attendance/Leave/Holiday of Employee
2- enter Overtime of The Employee.

Forth open "Payroll sheet according to perticular Month"

1- All salary detail and attendance will automatically carry forward. You have to enter only reimberance detail and other deduction detail only
2- Salry will generate as per your data.
3- Enter Salary Amount Which You have Paid.
4-Select Type of Payment.

Most of remaning sheet are monitoring or summary sheet.
30th March 2010 From India, New Delhi
Dear seniors,

Can anybody send me the details of Components for Over Time Calculation?
as per Factories Act.

Ex. Components like:

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

How do we calculate per hour???

Pl. guide me without delay

Thanks & Regards,
Avinash Kumar.K
22nd April 2010 From India, Bangalore
hi jiguparmar this is renu i am download your master salary sheet that relay great ,but there is some problem to access your file because of sheet protection , i request to you please give me that password through by using mail
this my id ,please send me as soon as possible.
Thanks & Regards,
15th June 2010 From India, Madras
I really appreciate the excel spreadsheet you shared but please could you explain to me how it works? Over time for me is paid at 1.5 times the normal hourly rate. O.t is anything more than 52hrs per week. What am I to 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? what of hour?
14th September 2010 From Kenya, Nairobi
Shraddha......... my OT calculator see if this can help you? waiting for your reply..........................................................
26th September 2010 From India, Anand

Attached Files
Membership is required for download. Create An Account First
File Type: xls OT Calculator.xls (34.5 KB, 3463 views)

BASIC+DA(if any)/26/8*2 for overtime wages to workers in case of staff it should be BASIC+DA(if any)/30/8*2
SO SIMPLE to undertsnd
26th September 2010 From India
Hello evryone,
perday sal (a) = basic/ no of days in month
incase of double OT: overtime rate per hour = a/4
incase of single OT: overtime rate per hour = a/8
now a simple thing:
ur ques was if a worker wrks frm 9am to 12am
just keep in mind that OT for 12 hrs of wrk i.e: 9am to 9pm =3
so 9am to 10pm = 4
and 9am to 11pm = 5
after 5hrs of OT you have to deduct 1/2 hr
so 9am to 12am = 5.5 OT Hrs
Kalpit Mahendra Mhatre
Larsen and Toubro Ltd.
APU-ONGC Project
HCP Division
21st October 2012 From India, Mumbai
Dear Sir, Kindly tell me why should we deduct half an hour after 5 hours of over time.
13th May 2013 From India, Mumbai
Dear friends,
My doubt is if an employee is called for duty on his weekly off and it also happens to be a national holiday, in which manner his payment will be made. Is he eligible for double overtime? In our nearby factories one day extra payment is made. Clarification is requested.
8th October 2016 From India, Ajmer
can any body help in this problem generally my company looks morning overtime and evening overtime saperate
work start time 08.30
work sign in time 09.45
work sign out time-6.30
work end time -7.30
for all above calcutation i need formula in this situation daily 8 hrs removed and extra should be calculated as over time to employees how to calculate and if any body knows the let me help in this regards
24th March 2017 From India, Hyderabad
I am working in Manufacturing company (Hyderabad) as a Accounts Manager.. My company is trying to pay the leave encasement for all staff ...My Quotation is ..
1) When the person eligible to apply the leave encasement (Is there any minimum days)
2) Sick Leaves they can encasement ??
3) Any Statutory Like ESI ,PF & Others applicable
The Above for Manufacturing unit ..
6th May 2017 From India, Hyderabad
Dear Kiran,
1) There is no specific timeline as to when a person should apply for encashment. As per the company policy you should be able to do, but in general practice usually during settlement / once in a year etc., companies will allow employees to apply for it.
Note- Being the reason for granting of leave is to refresh our-self in a given intervals and come back fresh to work.
2) In majority of the case, sick leaves are not allowed for encashment, but if your company policies allows it, you can go ahead and do it.
3) There is no involvement of PF / ESI ect into encashment, but Yes! Income tax is applicable, i.e any amount you encash are taxable.
In general leave policies are subjected to company practices, and it should have been mentioned in your HR policy documents itself."
22nd May 2017 From India, Bengaluru
Dear Suntek Corp Solutions Pvt. Ltd.,
Willing to pay your employees for the OT is a best practice and appreciate the same. While calculating OT we should always keep in mind how many hours extra a person has worked and how much are you willing to pay per hour. If I am company XXX paying employees for OT as Rs. 100/ hour then monthly calculate the total hours worked and multiply the same.
Note: You can also have an option for Compensatory Off in this case, where some employees would like to opt for a leave rather than getting paid extra. Also check this link: to know how greytHR helps in this process.
22nd May 2017 From India, Bengaluru
Basic/26 (working days) /8hrs*2= 1 hr overtime
26th May 2018 From India
Add Reply Start A New Discussion

Cite.Co - is a repository of information created by your industry peers and experienced seniors. Register Here and help by adding your inputs to this topic/query page.
Prime Sponsor: TALENTEDGE - Certification Courses for career growth from top institutes like IIM / XLRI direct to device (online digital learning)

About Us Advertise Contact Us
Privacy Policy Disclaimer Terms Of Service

All rights reserved @ 2019 Cite.Co™