work breaks from time worked formulas

LFULLER

New Member
Hi, I am trying to create a spread sheet for work times. I need to be able to create a formula that will subtract the break times from the time entered based on the span of time. For example, we have a 15 minute break from 10-10:15, a 30 min. break from 12-12:30, and another 15 min. break from 2-2:15. So, if an employee works from 8AM-4PM, then 2 15 minutes breaks, and a 30 minute break needs to be deducted from the total time worked. What we are looking for is a way for each break to be deducted from the time worked, based on the time entered, so if I enter time worked as 8-11, the only 1 15 minute would be deducted.
Can you help?

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Does this help at all?

A B C D E F G H I
1 Day Date Start Finish Gross Break 1 Break 2 Net Hrs Weekly
2 Sunday 17 September 2006 OFF 00:00 00:00 00:00 00:00
3 Monday 18 September 2006 09:25 18:10 08:45 00:30 00:45 07:30
4 Tuesday 19 September 2006 09:25 18:10 08:45 00:30 00:45 07:30
5 Wednesday 20 September 2006 09:00 18:10 09:10 00:30 00:45 07:55
6 Thursday 21 September 2006 09:25 21:10 11:45 00:30 00:45 10:30
7 Friday 22 September 2006 OFF 00:00 00:00 00:00 00:00
8 Saturday 23 September 2006 08:25 18:10 09:45 00:30 00:45 08:30 41:55

Sheet1

[Table-It] version 05 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
A2:A8 =TEXT(B2,"dddd")
E2:E8 =IF(C2="OFF",0,D2-C2)
F2:F8 =IF(C2="OFF",0,1/48)
G2:G8 =IF(C2="OFF",0,1/32)
H2:H8 =E2-F2-G2
I8    =SUM(H2:H8)

[Table-It] version 05 by Erik Van Geit``````

Sorry, I can't seem to understand how that will work. I am a newbie, and I can't make sense of what you wrote.

Sorry if it is not well formatted.

If you would like a copy of the sporeadsheet just e-mail me.

Take entry for 18 September, Start 09:25, finish 18:10, breaks of 30 minutes and 45 minutes. Total time at work is 18:10 - 09:25 = 8:45. Time less breaks = 8:45 - 0:30 - 0:45 = 7:30.

Replies
16
Views
811
Replies
2
Views
123
Replies
13
Views
828
Replies
5
Views
254
Replies
1
Views
254

1,216,102
Messages
6,128,853
Members
449,471
Latest member
lachbee

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.

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