Formula to add every 25-30 minutes overtime

[naveron]

So, I want to count overtime. An employee gets counted for overtime for every 30 minutes.
But we give them a little stretch, that they also get counted for 30 minutes even when they work overtime for 25 minutes.

For example:
John Doe checked in at 08:00 a.m and checkout at 05:25 p.m yesterday. He has 1 hour break time in between, so that means he has worked for 8 hours 25 minutes.
The normal work hours is 8 hours, so that means he has worked overtime for 25 minutes.

So, how the formula would be I wonder?

I hope somebody can help, thank you very much.

Btw, I attached a sample of how the table would look like, and I also figure out the simple formula to count the raw overtime.
However, what I need is, if their overtime is less than 25 minutes, then the overtime column would display as 0.
If their overtime is more than 25 minutes, it will display 00:30
If the overtime is 50 minutes, it will display as 01:00.
So its every 25-30 minutes... I think.

I hope the explanation is clear.

[FDibbins]

Try this...
=IF((E5-(D5-C5)-B5)-F5 < TIME(0,25,0),0,(E5-(D5-C5)-B5)-F5)

[naveron]

Hi FDibbins,

Thank you very much for your reply.

I've tried the formula you suggest, it worked for the < 25 minutes overtime (it displays as 0).
However, the 25 minutes overtime still display as 25 minutes, not 30 minutes,
and the 50 minutes overtime still display as 50 minutes, not 1 hour.

Appreciate your help a lot. I hope there will be a way to figure this out. Thank you.

[Gregb11]

Why does 50 minutes go to 1 hour and not 55 minutes? I'm assuming 49 minutes goes to 30 minutes? what does 1 hour and 15 minutes go to (which is 25 minutes past 50 minutes OT)?

[naveron]

Hi Gregb11,

Because I was thinking of multiplication of 25-30 minutes. So if the overtime reached 50 minutes (which is twice 25 minutes), then it will be rounded as 1 hour.

That's what I was thinking.

[Gregb11]

You didn't answer the other question. So what if it's 1 hour and 15 minutes (which is 3 times 25). Using the same logic that would go to 1.5 hours, and so on. If you continue with this logic, I don't believe it will make sense.

[naveron]

Gregb11:

Oh, didn't think that way or that far... umm, nah, I guess I don't have a clue. lol

I guess your previous question made more sense, 55 minutes round up as 1 hour.

[Gregb11]

So it sounds like what you want to do is if the person works less than 25 minutes OT, that it's rounded down to 0. If they work from 25 minutes to less than 55 minutes OT, then it rounds (up or down) to 30 minutes. If they work 55 minutes to less than 1 hour and 25 minutes, it goes to 1 hour, and so on. Is this correct?

[naveron]

Yes, you get what I mean perfectly. Thank you!

[Gregb11]

This formula should work:
=MROUND(ABS(H7-25/60/60),30/(60*24))

You can shorten it by doing the math, but I left it like this so you get an idea of how I got to the formula (subtracting 25 minutes and then rounding to a multiple of 30 minutes).

[naveron]

Gregb11:

Just tried it, worked great! Thank you so much!

[jason.b75]

Try changing your formula in G5 to

Please Login or Register  to view this content.