Another timesheet formual question.

[bajra]

I have a basic working knowledge of excel (using excel 2010). I'm trying to create a timesheet. It needs to have room for 2 breaks and a lunch. here's what i have
=(J4-C4)-(G4-F4)-(E4-D4)-(I4-H4)

My problem is with the 2 breaks (e4-d4 & i4-h4) i need it to calculate if the break is longer than 10 minutes, to just subtract anything in excess of ten minutes for each break. If this has already been covered, if someone could point me in the right direction, my search powers have escaped me the past couple days.

 

[bryonwoods30]

what values are you putting in the cells? can you give an example of what the data in very cell in your formula would be and what the result is that you want to see.

 

[scottylad2]

My problem is with the 2 breaks (e4-d4 & i4-h4) i need it to calculate if the break is longer than 10 minutes, to just subtract anything in excess of ten minutes for each break. If this has already been covered, if someone could point me in the right direction, my search powers have escaped me the past couple days.

if the combined breaks are more than 10 mins or 2 x 10 mins, only subtract the excess for greater than 10 mins on each?

 

[bajra]

Each break is allowed ten minutes (2 x10),

Here's what the data would look like

a B C D E F G

<table border="0" cellpadding="0" cellspacing="0" width="848"><col style="mso-width-source:userset;mso-width-alt:2925; width:60pt" span="2" width="80"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.75pt" height="21"> <td class="xl70" style="height:15.75pt;width:60pt" height="21" width="80">Day</td> <td class="xl72" style="border-left:none;width:60pt" width="80">Date</td> <td class="xl67" style="width:51pt" width="68">Shift Start</td> <td class="xl67" style="border-left:none;width:55pt" width="73">Break start</td> <td class="xl67" style="border-left:none;width:51pt" width="68">Break End</td> <td class="xl67" style="border-left:none;width:56pt" width="75">Lunch Start</td> <td class="xl67" style="border-left:none;width:52pt" width="69">Lunch End</td> <td class="xl67" style="border-left:none;width:56pt" width="74">Break Start</td> <td class="xl67" style="border-left:none;width:51pt" width="68">Break End</td> <td class="xl67" style="border-left:none;width:49pt" width="65">Shift End </td> <td class="xl67" style="border-left:none;width:48pt" width="64">Hrs</td> <td class="xl71" style="width:48pt" width="64"> daily OT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl75" style="height:15.0pt;border-top:none" height="20">Monday</td> <td class="xl74" style="border-top:none" align="right">3/29/2011</td> <td class="xl73" align="right">8:00 AM</td> <td class="xl65" style="border-left:none" align="right">9:00
AM</td> <td class="xl65" style="border-left:none" align="right">9:10 AM</td> <td class="xl65" style="border-left:none" align="right">12:00 PM</td> <td class="xl65" style="border-left:none" align="right">1:00 PM</td> <td class="xl65" style="border-left:none" align="right">3:00
PM</td> <td class="xl66" style="border-left:none" align="right">3:10 PM</td> <td class="xl66" align="right">5:00 PM</td> <td class="xl68" style="border-top:none" align="right">7:40</td> <td class="xl69" style="border-top:none" align="center">########</td> </tr> </tbody></table>

 

[scottylad2]

Each break is allowed ten minutes (2 x10),

Here's what the data would look like

a B C D E F G

<table border="0" cellpadding="0" cellspacing="0" width="848"><colgroup><col style="mso-width-source:userset;mso-width-alt:2925; width:60pt" span="2" width="80"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:15.75pt" height="21"> <td class="xl70" style="height:15.75pt;width:60pt" height="21" width="80">Day</td> <td class="xl72" style="border-left:none;width:60pt" width="80">Date</td> <td class="xl67" style="width:51pt" width="68">Shift Start</td> <td class="xl67" style="border-left:none;width:55pt" width="73">Break start</td> <td class="xl67" style="border-left:none;width:51pt" width="68">Break End</td> <td class="xl67" style="border-left:none;width:56pt" width="75">Lunch Start</td> <td class="xl67" style="border-left:none;width:52pt" width="69">Lunch End</td> <td class="xl67" style="border-left:none;width:56pt" width="74">Break Start</td> <td class="xl67" style="border-left:none;width:51pt" width="68">Break End</td> <td class="xl67" style="border-left:none;width:49pt" width="65">Shift End </td> <td class="xl67" style="border-left:none;width:48pt" width="64">Hrs</td> <td class="xl71" style="width:48pt" width="64"> daily OT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl75" style="height:15.0pt;border-top:none" height="20">Monday</td> <td class="xl74" style="border-top:none" align="right">3/29/2011</td> <td class="xl73" align="right">8:00 AM</td> <td class="xl65" style="border-left:none" align="right">9:00
AM</td> <td class="xl65" style="border-left:none" align="right">9:10 AM</td> <td class="xl65" style="border-left:none" align="right">12:00 PM</td> <td class="xl65" style="border-left:none" align="right">1:00 PM</td> <td class="xl65" style="border-left:none" align="right">3:00
PM</td> <td class="xl66" style="border-left:none" align="right">3:10 PM</td> <td class="xl66" align="right">5:00 PM</td> <td class="xl68" style="border-top:none" align="right">7:40</td> <td class="xl69" style="border-top:none" align="center">########</td> </tr> </tbody></table>

Are you including the breaks in what you want as your total? if your break is 2x10 mins and someone takes 2 x 20 minutes, what would your expected answer be? Your showing in your sample that your including the breaks in your daily hours, 7:40 what I'm not quite getting is when you say if the breaks go over 10 mins just add them.....is all you want to show in your daily totals any time over a scheduled break or in the scenario i showed where a total of 40 mins were taken instead of twenty do you wish your daily total to show you've taken too much break or what?

 

[bajra]

If i take a single break all day that only last 10 minutes then my total should be 8 hours. If i take 2 breaks that last ten minutes each. again my total should be 8hrs. if i take my first break and it's 15 minutes and my second break is 10 minutes then my total day should be 7:55 hrs (less the 5 minutes that my first break went over)

Same thing if i take a 15 minutes break for my first break and no second break, my time should still reflect 7:55 hr for having gone over the alotted ten minutes for the first break.

 

[scottylad2]

Ok I think I get what your looking for, this I think acheives that. If you take only 2 x 10 min breaks, it will give you an 8 hour shift. If you go over in either break, you'll end up with a less than 8 hour day. THe only thing i've not accounted for is no breaks taken in a day, if thats the case it will still count as an 8 hour day, or rather there will be a straight count of all the hours worked, finish time minus start time. If you need this tweaked let me know. (the formula looks long, but i've added in an Or arrgument so the formula doesn't calculate till all the times are ini

=(K26-D26)-IF(F26-E26>H21,(F26-E26)-H21,0)-(IF(J26-I26>H21,(J26-I26)-H21,0))-(H26-G26)

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
25	Day	Date	Start	Break Out	Break in	Lunch start	lunch end	Break start	Break end	Finnish	Total
26	Friday	01/04/2011	08:00	09:00	09:10	12:00	13:00	15:00	15:10	17:00	8:00
Sheet1

THe above will show negative time error ########### until all fields are filled in

below including the Or function should eradicate them. Only use this version if your filling in as you go through the day, if you're doing it all at once then use the first formula

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
25	Day	Date	Start	Break Out	Break in	Lunch start	lunch end	Break start	Break end	Finnish	Total
26	Friday	01/04/2011	08:00	09:00	09:10	12:00	13:00	15:00	15:10	17:00	8:00
Sheet1

and what it will show if you have to long a break, lets say you took 2 x 25 minutes

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
25	Day	Date	Start	Break Out	Break in	Lunch start	lunch end	Break start	Break end	Finnish	Total
26	Friday	01/04/2011	08:00	09:00	09:25	12:00	13:00	15:00	15:25	17:00	7:30
Sheet1

 

[scottylad2]

Forgot to say, in the formulas posted I put 00:10 in H21 and used that as a reference in the part of the formula counting the breaks

 

[bajra]

Ok I think I get what your looking for, this I think acheives that. If you take only 2 x 10 min breaks, it will give you an 8 hour shift. If you go over in either break, you'll end up with a less than 8 hour day. THe only thing i've not accounted for is no breaks taken in a day, if thats the case it will still count as an 8 hour day, or rather there will be a straight count of all the hours worked, finish time minus start time. If you need this tweaked let me know. (the formula looks long, but i've added in an Or arrgument so the formula doesn't calculate till all the times are in

=(K26-D26)-IF(F26-E26>H21,(F26-E26)-H21,0)-(IF(J26-I26>H21,(J26-I26)-H21,0))-(H26-G26)


Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:76px;"><col style="width:78.4px;"><col style="width:78.4px;"><col style="width:78.4px;"><col style="width:78.4px;"><col style="width:78.4px;"><col style="width:78.4px;"><col style="width:78.4px;"><col style="width:78.4px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td style="font-weight:bold; border-style:solid; border-width:1px; border-color:#000000; ">Day</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Date</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Start</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Break Out</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Break in</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Lunch start</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">lunch end</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Break start</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Break end</td><td style="font-weight:bold; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Finnish</td><td style="font-weight:bold; text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-top-color:#000000; border-right-color:#000000; border-bottom-color:#000000; ">Total</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-right-color:#000000; border-bottom-color:#000000; border-left-color:#000000; ">Friday</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">01/04/2011</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">08:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">09:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">09:25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">12:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">13:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">15:00</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">15:25</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">17:00</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-right-width:1px; border-bottom-width:1px; border-right-color:#000000; border-bottom-color:#000000; ">7:30</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>L26</td><td>=(K26-D26)-IF(F26-E26>H21,(F26-E26)-H21,0)-(IF(J26-I26>H21,(J26-I26)-H21,0))-(H26-G26)</td></tr></tbody></table></td></tr></tbody></table>
you're a genius! this is what i needed. I was thinking i was going to need some IF statements in there but i was unsure of the proper formatting and adding the reference field (h21) was alot more elegant than my neanderthalic formulas. Thanks a bunch!

 

[bajra]

=IF(OR(J3="",I3="",H3="",F3="",E3="",D3=""),"",((J3-C3)-IF(E3-D3>K1,(E3-D3)-K1,0)-(IF(I3-H3>K1,(I3-H3)-K1,0))-(G3-F3)))

here's the formula i've plugged into my calculations, the only problem i'm having now is if no break or lunch is taken, it's not showing anything. Did i punch in something wrong? (my OR statement fields are Shift End, 2nd break end, 2nd break start, lunch start, 1st break end, 1st break start)

=((J4-C4)-IF(E4-D4>K1,(E4-D4)-K1,0)-(IF(I4-H4>K1,(I4-H4)-K1,0))-(G4-F4)) <- this version shows a the results if breaks or lunch are missing.

I like it not showing a negative value. it just looks cleaner and you've already helped me get to a formula that works properly. i was just wondering if i'd punched the OR functions in wrong or if it could be tweaked a little.
thanks!