Timecard Condition Formula?

iexcel

New Member
Hi all, I need a formula for the following conditions: I’m doing a timecard. Our regular total hours per day is 7:30 hours. So if you worked 8 hours, you get 7:30 regular hours in cell F6, 0:30 minutes regular rate overtime in cell G6. Lets say you worked 9 hours, you get 7:30 regular hours in cell F6, 0:30 minutes regular rate overtime in cell G6, 1:00 hour time and half overtime in cell H6. This is where I can't figure out, when cell G6 automatically fills in the 0:30 minutes regular rate overtime when you worked more than 8 hours. Please help. Thank You Very Much. John.

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

fairwinds

MrExcel MVP
Hi,

Try this:
Book1
EFGHI
5TotalhRegularRegularOTExtraOT
609:0007:3000:3001:00
7
8
Sheet1

iexcel

New Member
Thank you for responding, This formula works when someone works over 8 hours. With this formula and a combination of another,what if someones works under 7:30 hours a day,cell E6, Cell G6 should be 0:00. Please help.
Thank You, John

Chitosunday

Well-known Member
cell g6 formula =MIN("0:30",MAX(0,E6-F6))
cell h6 formula =MAX(0,E6-F6-G6)

fairwinds

MrExcel MVP
Hi,

Chito: I added your formulas below, they work too but I fail to see any improvement.
03_23.xls
EFGH
5TotalhRegularRegularOTExtraOT
603:5003:5000:0000:00
7
800:0000:00
Sheet2

Chitosunday

Well-known Member
Fairwind, I check your formula and you are right. Your original formula is correct. Sorry, I didn't test it before as the OP immediately replied there is something wrong on values less than 7:30.

Replies
2
Views
204
Replies
9
Views
81
Replies
1
Views
97
Replies
7
Views
453
Replies
25
Views
304

1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back