work breaks from time worked formulas

LFULLER

New Member
Joined
Sep 25, 2006
Messages
2
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

LFULLER

New Member
Joined
Sep 25, 2006
Messages
2
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

Forum statistics

Threads
1,141,718
Messages
5,708,072
Members
421,543
Latest member
SGM

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