# counting work hours with break

#### plit

##### New Member
Hello, i'm newcomer. Used mrexcel articles for a long time, for different kind of issues, but now have this prob with no luck to solve/find solution. So had to register, and humbly ask ur suggestion(s)

Ok, we have work table looking like that

A B C
Name Time Start End
1 First 11:00 AM 18:00 PM
2 Second 18:00 PM 22:00 PM
3 Third off

And in next row, i have to count, how much time did they spent on work.

So like:

4 First ?
5 Second ?
6 Third ?

Thing is, that for all, it's not so hard to count, if only real time of being on workplace is counting, using formula in B4 field =IF(B1="off", "", SUM(C1-B2))

But as we have in every schedule for employee, one hour of break. I have to remove one hour from that. Using =IF(B1="off", "", SUM((C1-B2)-"1:00))

Now the Question. As for example, people could come to work for 4 hours only ie from 10:00 am till 14:00 pm. For that case, employee doesnt have break hour. How can i make formula work, that if B2-C1 is less than 4:00, it wont remove 1:00 hour from it.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### fairwinds

##### MrExcel MVP
Welcome to the board!

Try:

=B1-A1-(B1-A1>=--"4:00")*"1:00"

#### plit

##### New Member
thanx fairwinds

Now have new issue with same sheet, when i'm trying to count full hours of work for that date, and we have more of 24h of total working hours, it converts it to "next day", and if like we had 26h, it'll show only 2h. any fast tip? :P

#### fairwinds

##### MrExcel MVP
For handeling times > 24 hours, custom format formula cell as [h]:mm

Also to handle times crossing midnight:

=B1-A1+(B1 < A1)-(B1-A1+(B1 < A1)>=--"4:00")*"1:00"

#### plit

##### New Member
u're the star. ray:

Replies
6
Views
146
Replies
4
Views
236
Replies
2
Views
216
Replies
10
Views
349
Replies
3
Views
298

1,196,010
Messages
6,012,841
Members
441,733
Latest member
MartijnB

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