Help Please...!! How To Separate Regular & Overtime Hours From Total Monthly Hours

Hi All,

If I have Total House in A1 and I want B1 to show me Regular Hours and C1 To take out remaining Hours as Overtime and show me.

here In my country we have OFF on Fridays but still Fridays are paid as regular Hours
Regular working Hours are 8 and the above all is calculated as Overtime.
Monthly Total Regular Hours are 248 (8x31=248)

Suppose...
If A1 is showing Total 288 Hours...
So B1 should show 248 but how???
And C1 should show the remaining 40...

I want to know the formula or anyhow needs that to be done for my work purpose.

thanks in advance guys.

regards,
IFFY

Try

B1: =MIN(248,A1)
C1: =A1-B1

Hey Jonmo,,,

Thanks for your reply and it really helped me but there is a major problem that now I can clear to you for understanding more.

if an employee's total working hour are 230 just suppose but still there is overtime needed to be paid to him as overtime rate is different then the regular rate. what to do in such condition because your given formula will work on exact 248 Hours only not less then that.

regards,
IFTIHAR

I don't see the problem,
B1 shows the 'Regular' worked hours (defined as hours up to 248 in a 31 day period).

So if Regular hours are paid at say 15.00 per hour, and overtime paid at 22.50
B1: =MIN(248,A1)*15.00
C1: =(A1-B1)*22.50

My Friends i'm sorry if I could not clear my question to you. let me try again.
actually; what I want to know is......
if the total monthly hours are less then 248 (31 days) so its for sure that there are some absents at work and some of the days are filled with overtime.
lets suppose if total working hours (regular & overtime) are 232; so how can you segregate the regular and overtime hours from this value?

Ok, so there are 2 variables then.
How Many Hours Worked
How Many Days Worked.

So lets say A1 is How Many Days they worked, and B1 is Total Hours Worked.

Total Regular Hours
C1: =MIN(A1*8,B1)

Remaining Overtime Hours
D1: =B1-C1