Time Card Formula Help

gdillon

Board Regular
Joined
Feb 5, 2006
Messages
129
On the time card below, I need a way to calculate Regular time and/or Overtime. Overtime only kicks in after 40 hours, right now you will see the time card doesn't separate the regular time to overtime. What is the best way to calculate Overtime and separate it not only on a total week but also show it on the days that OT is present? You see line 16 it has cells marked Reg and OT for this, I just need the solution to make it happen.
Thank you in advance
Weekly time sheet with breaks1
BCDEFGHIJKLMNOPQRST
8
9SundayMondayTuesdayWednesdayThursdayFridaySaturday
105/29/20055/30/20055/31/20056/1/20056/2/20056/3/20056/4/2005
11TimeIn6:00AMTotal6:00AMTotal6:00AMTotal6:00AMTotal6:00AMTotal6:00AMTotal6:00AMTotal
12TimeOut12:00PM6.0012:00PM6.0012:00PM6.0012:00PM6.0012:00PM6.0012:00PM6.0012:00PM6.00
13MealBreak
14TimeIn1:00PMTotal1:00PMTotal1:00PMTotal1:00PMTotal1:00PMTotal1:00PMTotal1:00PMTotalRegHoursOTHours
15TimeOut5:00PM4.005:00PM4.005:00PM4.005:00PM4.005:00PM4.005:00PM4.005:00PM4.00
16RegOTRegOTRegOTRegOTRegOTRegOTRegOT
17Total10.0010.0010.0010.0010.0010.0010.0070.000.00
Weekly Time Sheet
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
At what point does regular time become overtime?

Assuming anything above 8 hours is overtime on a Monday try, in G17

=MIN(8,H12+H15)

and in H17

=H12+H15-G17
 

gdillon

Board Regular
Joined
Feb 5, 2006
Messages
129
Time Card Help

Overtime starts after 40 hours, So I need to have a formula that will separate the Regular time and Over Time when it reaches the threshold of over 40 hours in a week. But need to show breakdown of Regular Time and Overtime per day.
 

Forum statistics

Threads
1,181,102
Messages
5,928,063
Members
436,586
Latest member
latintxn

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top