Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

1. Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

I think I have finally figured out how to get the calculations done for my time sheet into 3 categories, Regular Hours, OT Hours & Double Time Hours.
I'm running into a problem with the OT hours. I need it to show only a Maximum of 4 hours the remainder go into Double Time.

here is how my sheet looks with in/out times
TimeSheetPic.PNG

Here are my formulas for each:
Reg Time
=IFERROR(IF((((C15-B15)+(E15-D15))*24)>8,8,((C15-B15)+(E15-D15))*24), "")
OT ( I need this to max out at 4 hours)
=IFERROR(IF((((C15-B15)+(E15-D15))*24)>8,((C15-B15)+(E15-D15))*24-8,0), "")
Double Time
=IFERROR(IF((((C15-B15)+(E15-D15))*24)>12,((C15-B15)+(E15-D15))*24-12,0), "")

Any help is appreciated!

2. Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

Hi, NiecieD!

Try this formulas:
Reg Time [F15] : =MIN(8,24*(C15-B15+E15-D15))
OT [G15] : =MIN(4,24*(C15-B15+E15-D15)-F15)
Double Time [H15] : =MAX(,24*(C15-B15+E15-D15)-F15-G15)

Blessings!

3. Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

OT column is only calculating 2 hours and 0 Hours for DT when the final out time is 11p or 10p when using that formula.

If it matters the cells for in/out are set to number w/2decimal places since there can be quarter hour uses.

4. Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

What is your expected results? Check file with formulas of post # 2 applied. Blessings!

5. Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

Originally Posted by johnmpl
What is your expected results? Check file with formulas of post # 2 applied. Blessings!
OMG I'm so dumb right now! I did not make sure the line was correct. I had applied it to line 19 and it was looking at 15 (as I just did a copy paste)! this works great thank you jihnmpl!

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1