Need help creating timesheet

[jaugent27]

I want to create a time sheet with the following columns...

Time In : Lunch Out, Lunch In : Time Out

So basically, people put time in as 8:05 am and time out as 4:45 pm. I need the formula to calculate that this equals 8 hours and 40 minutes plus subtract out if the person takes a lunch. I then need to have a column that keeps tabs if they went over 8 hours in the day. So in the example this would say 40 minutes. I then need to sum up both the total hours and additional time hours. Im sure this is possible and I would be very grateful for your help.

Categories for each workday will be:

Time In: Lunch Out: Lunch In : Time Out : Total Time : Additional Time

Thanks!

 

[VoG]

I haven't read this thread in detail so apologies if I am totally on the wrong track:

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">19:54</td><td style="text-align:right; ">07:11</td><td style="text-align:right; ">11:17</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=MOD(B1-A1,1)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


Or use the 1904 date system which allows negative durations.

 

[c_m]

Ah, negative times are displayed as # signs. When i change the format to a number, it does not take into account the minutes. So for example, i only entered one day's time, 10.5 hours. It displayed -30 as opposed to -29:50 hours which is what I would like it to do.

Try this:
=if(LEFT(G8,FIND(":",G8)-1)-40<=0,"",LEFT(G8,FIND(":",G8)-1)-40)
just with enter.
Does this help?

 

[jaugent27]

Close.

It still is not giving me minutes.

Also it only gives me hours over 40, not under

 

[c_m]

Close.

It still is not giving me minutes.

Also it only gives me hours over 40, not under

See if this solution is okay.
<title>Excel Jeanie HTML</title>
<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" 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: 56px;"> <col style="width: 72px;"> <col style="width: 62px;"> <col style="width: 65px;"> <col style="width: 75px;"> <col style="width: 109px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="font-weight: bold; text-align: center;">Date</td> <td style="font-weight: bold; text-align: center;">Day</td> <td style="font-weight: bold;">Time In</td> <td style="font-weight: bold;">Lunch Out</td> <td style="font-weight: bold;">Lunch In</td> <td style="font-weight: bold;">Time Out</td> <td style="font-weight: bold;">Total Time</td> <td style="font-weight: bold;">Additional Time</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">2/2/2009</td> <td>Monday</td> <td style="text-align: right;">8:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">5:40 PM</td> <td style="text-align: right;">9:40</td> <td style="text-align: right;">1:40</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">2/3/2009</td> <td>Tuesday</td> <td style="text-align: right;">8:05 AM</td> <td style="text-align: right;">12:30 PM</td> <td style="text-align: right;">1:15 PM</td> <td style="text-align: right;">5:00 PM</td> <td style="text-align: right;">8:10</td> <td style="text-align: right;">0:10</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">2/4/2009</td> <td>Wednesday</td> <td style="text-align: right;">8:00 AM</td> <td style="text-align: right;">12:00 PM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">6:00 PM</td> <td style="text-align: right;">9:00</td> <td style="text-align: right;">1:00</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">2/5/2009</td> <td>Thursday</td> <td style="text-align: right;">9:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">6:00 PM</td> <td style="text-align: right;">9:00</td> <td style="text-align: right;">1:00</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">2/6/2009</td> <td>Friday</td> <td style="text-align: right;">7:30 AM</td> <td style="text-align: right;">11:30 AM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">4:00 PM</td> <td style="text-align: right;">7:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td style="font-weight: bold;">Total</td> <td style="text-align: right;">42:50</td> <td style="text-align: right;">2:50</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="text-align: right;">2/2/2009</td> <td>Monday</td> <td style="text-align: right;">8:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">3:40 PM</td> <td style="text-align: right;">7:40</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="text-align: right;">2/3/2009</td> <td>Tuesday</td> <td style="text-align: right;">8:00 AM</td> <td style="text-align: right;">12:30 PM</td> <td style="text-align: right;">1:15 PM</td> <td style="text-align: right;">2:45 PM</td> <td style="text-align: right;">6:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="text-align: right;">2/4/2009</td> <td>Wednesday</td> <td style="text-align: right;">8:00 AM</td> <td style="text-align: right;">12:00 PM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">4:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="text-align: right;">2/5/2009</td> <td>Thursday</td> <td style="text-align: right;">9:00 AM</td> <td> </td> <td> </td> <td style="text-align: right;">4:00 PM</td> <td style="text-align: right;">7:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="text-align: right;">2/6/2009</td> <td>Friday</td> <td style="text-align: right;">7:30 AM</td> <td style="text-align: right;">11:30 AM</td> <td style="text-align: right;">1:00 PM</td> <td style="text-align: right;">4:00 PM</td> <td style="text-align: right;">7:00</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td style="font-weight: bold;">Total</td> <td style="text-align: right;">31:40</td> <td style="color: rgb(255, 0, 0); text-align: right;">-8:20</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>G8</td> <td>{=INT((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6)))&":"&ROUND(((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6))-INT((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6))))*60,0)}</td></tr> <tr> <td>H8</td> <td>=IF(AND(LEFT(G8,FIND(":",G8)-1)-40>=0,RIGHT(G8,LEN(G8)-FIND(":",G8))>0),LEFT(G8,FIND(":",G8)-1)-40&":"&RIGHT(G8,LEN(G8)-FIND(":",G8)),LEFT(G8,FIND(":",G8)-1)-40+1&":"&60-RIGHT(G8,LEN(G8)-FIND(":",G8)))</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
I have shown two sets here. To convert the additional hour font to red when the total hours worked is less than 40, like in the example set 2 shown above, select H17, goto format - conditional format - type formula: =LEFT(H17,1)="-"
select format and select font color red.

Hope this helped.

 

[Krishnakumar]

in G2:

=F2-C2-(E2-D2)

H2:

=MAX(0,G2-"8:00"+0)

G8:

=SUM(G2:G6)

H8:

=MAX(0,G8-"40:00"+0)