counting work hours with break

[plit]

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.

 

[fairwinds]

Welcome to the board!

Try:

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

 

[plit]

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]

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]

man, u've made my day!
u're the star. ray: