# Need to create a formula that deals with time

#### Weasel

##### Board Regular
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

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

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

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.

Thanks, that fix it!

Replies
5
Views
518
Replies
12
Views
613
Replies
1
Views
901
Replies
1
Views
492
Replies
4
Views
281

1,221,417
Messages
6,159,789
Members
451,589
Latest member
Harold14

### 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.

### Which adblocker are you using?

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