Need to create a formula that deals with time

Weasel

Board Regular
Joined
Sep 13, 2005
Messages
58
I'm working in an excel sheet that is for payroll. For each person it lists the amount of hours they worked for each day (Saturday - Friday). It looks like this:

Code:
Column A    Column B        Column C

Weekday     Time Worked    Overtime
Saturday        4:13 (4 hours, 13 minutes)
Sunday          8:12
Monday          9:20
Total

Anyone who works over 8:00 hours in a day gets overtime. So using my example above, the person should get 0:12 overtime on Sunday and 1:20 overtime on Monday. I need a formula that will automatically put the 0:12 and 1:20 in Column C. I also need a formula that will add up the Time Worked and Overtime columns. Thanks in advance for any help.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,562
Office Version
  1. 365
Platform
  1. Windows
Weasel

Something like this?

1. E1 contains 8:00
2. C2 (copied down): =MAX(B2-$E$1,0)
3. B4 (copied across): =SUM(B2:B4)
All formatted as hh:mm
Mr Excel.xls
ABCDEF
1Time WorkedOvertime8:00
24:120:00
38:120:12
49:201:20
521:441:32
6
Overtime
 

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
Here's an option that doesn't require the overtime value to be stored in a cell. Assuming that all the time values are formatted as [h]:mm...
Book1
ABCD
1WeekdayTime WorkedOvertime
2Saturday4:13 
3Sunday8:120:12
4Monday9:201:20
5Total21:451:32
Sheet1
 

Weasel

Board Regular
Joined
Sep 13, 2005
Messages
58
I can't get the total formula to work properly since one the total goes over 24 it resets it to 0 since it thinks past midnight. Is there a way to get it to work properly? Thanks
 

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
Make sure that all time values are formatted as [h]:mm. Highlight all the time values, including the total values, right click and select "Format Cells...". In the "Number" tab choose "Custom" as the Category and enter "[h]:mm" as the Type, and select okay. This should fix the problem.

Note that h:mm format WILL NOT WORK. You must use [h]:mm.
 

Forum statistics

Threads
1,137,366
Messages
5,681,072
Members
419,950
Latest member
BeckiJae

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