V.Raghunathan
Process Industry Consultant / Soft Skill
Ravind7@gmail.com
Arnav13
Private Job
Flowerpower@28
Operations

Cite.Co is a repository of information created by your industry peers and experienced seniors sharing their experience and insights.
Dear Brothers / Sisters,
I am having problem creating formula for OT calculation on the spread sheet. Unable to link Normal (Y) or Rest Day / PH (Y) into the rate calculation. Is there any way a formula can be created if I work on a normal day only the normal day OT rate can be updated and auto compute.
Ravindran Raman
(ravind@tbsb.com.my)

Attached Files
 Membership is required for download. Create An Account First TBSB OT.xls (26.0 KB, 1711 views)

Good Morning All,
I have managed to insert the formula for calculation but unable to capture the 0.5 hrs difference as it only gives in round figure...eg...2.5 hrs becomes 3.0 hours.
Thank you.
Ravin

Attached Files
 Membership is required for download. Create An Account First TBSB OT.xls (30.5 KB, 623 views)

Dear Ravin,

I have reworked your excel and attached it with this mail.

While you intend taking the time values, you have chosen ordinary numbers that can take negative values.

To know the difference keep the cursor on any number that you have entered and right click the mouse.

Choose FORMAT cells from the menu.

Thereafter choose NUMBERS from the menu.

You will find that you have taken ordinary numbers.

Now choose any TIME number that I have entered.

It has been chosen as Time to suit your requirement.

In your calculation 6.00 AM to 6.50 AM is intended to show 50 minutes difference.

That is 50/60 of an hour.

However since you have chosen numbers Computer treats it as half !!

My way of calculation may look long.

But it is done in steps.

It will work for all cases as long as End Time is bigger than Start Time.

If you get odd situations like 23 hours to 3:00 hours then you have to make changes in the formula.

I am unable to attach the Xl sheet as some error is coming.

So I have put the correction in a pptx

V.Raghunathan

Attached Files
 Membership is required for download. Create An Account First OT cal with TIME input.pptx (51.9 KB, 892 views)

Mr Ravi,
This is regarding my Xl sheet that I sent to your id.
Was it useful?
If it is OK - it gives an alternative route - please post the same from your end.
As I mentioned above, I had some difficulty in attaching the Xl file with my calculations.
If you can post what I had sent you it will benefit other readers.
V.Raghunathan

Dear Mr Raghu,
Managed to open the XL file but unable to open the PPTx.
The formula able to compute the OT hours but I need input the time as 7.50 instead of 7.30 of example 7.00 to 7.30 is 1/2 hour, tried to change the time formula to input 7.30 instead of 7.50 capture the actual 1/2 hour but unable to make changes.
I have redone the Excel sheet and added few other details to compute the OT rates and total Income.
Thank you Sir,
Ravindran.

Attached Files
 Membership is required for download. Create An Account First Revised OT Format.xls (41.5 KB, 1337 views)

Dear Mr Ravindran,
I checked the formula in the H column of your spread sheet.
It will work well with whole numbers.
It will not work with fractions.
In Mathematics anything that you write after a point is a decimal ( one tenth).
If you write two places after a point then it is one hundredth.
The formula you have used is taking the input as numbers and not as TIME.
As I suggested keep the cursor in any cell in the H column.
Right click and then choose FORMAT cells.
You will find the selection highlighted is NUMBERS.
So the alternative way is to follow the way I have suggested.
Take time inputs as hh:mm ( ignoring seconds)
If you want some other solution you can check with other Xl experts.
V.Raghunathan

Dear Mr Raghu, Based your suggestion and with the help of few friends, managed to input the actual time in and time out, to subtract the OT hours for calculations. Attach the format. Regards, Ravin

Attached Files
 Membership is required for download. Create An Account First OT Calculation Format.xls (47.5 KB, 1600 views)

Dear Mr Ravindran,
I am glad that you persisted and came out with a different solution.
The formula bar shows a long formula but the format adopted is very simple and user friendly.
I am sure other readers with similar requirement would stand benefited.
V.Raghunathan

Hi I am calculation overtime on basic salary but my auditor point is overtime should be calculate on gross salary not basic salary as per Punjab shop and commercial act please suggest. Regards Arnav
Greetings Arnav,
We calculate overtime based on fixed basic salary and that is the requirement by law.
I have attached for you an article related to salary and overtime in India.
We have many seniors in the forum who are experts in this field and wait for their response.
All the best

Attached Files
 Membership is required for download. Create An Account First Work Hours and Overtime Pay in India.doc (37.5 KB, 1090 views)

This discussion thread is closed. If you want to continue this discussion or have a follow up question, please post it on the network.
Add the url of this thread if you want to cite this discussion.