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!

 

[c_m]

Hi jaugent27,
Welcome to the Board.

Are you looking for something like this?
<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> </td> <td> </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;">4:45 PM</td> <td style="text-align: right;">7:55</td> <td> </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></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>G2</td> <td>=F2-C2-E2+D2</td></tr> <tr> <td>H2</td> <td>=IF(G2>VALUE("8:00"),G2-VALUE("8:00"),"")</td></tr></tbody></table></td></tr></tbody></table>
After using these formula in G2 and H2, custom format both cells to h:mm and drag the formula down.

Hope this helped.

 

[jaugent27]

Thanks! One more thing...

If I want to add the TOTAL TIME to see if they worked a 40 hour week (need to reflect whether they worked under or over 40 hours/ week including Additional time (if necessary)

In my sheet, I had TOTAL TIMES of

9:40
9:30
9:00
9:52
6:45

I tried to SUM all these cells and it spit out 20:47. This doesn't make sense. Need just a bit more help.

Thanks!

 

[nbrcrunch]

What you need to realize is that you are actually looking at in each cell is a time value, not a duration. So yes, when you add those up, that is the correct total. Select G2 and I bet you will see 9:40:00 AM in the formula bar.

The following will give you the correct total for column G. After entering the formula, finish it with Ctrl-Shift-Enter (not just the Enter key).

=(SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6))

 

[c_m]

nbrcrunch's formula is correct, but it gives answer in decimal. If you want answer in traditional h:mm format, try this:

=INT((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6)))&":"&TRUNC(((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6))-INT((SUM(MINUTE(G2:G6))/60)+SUM(HOUR(G2:G6))))*60,0)

confirm the formula with control+shift+enter

 

[jaugent27]

Awesome!

How do I then take the total of the 5 day work week and compare it to a normal 40 hour week spitting out a deviation from the 40 hour week.

So if you worked 42 hours, the cell would say 2.

If you worked 38 hours, the cell would say (2).

Thanks!

 

[c_m]

Awesome!

How do I then take the total of the 5 day work week and compare it to a normal 40 hour week spitting out a deviation from the 40 hour week.

So if you worked 42 hours, the cell would say 2.

If you worked 38 hours, the cell would say (2).

Thanks!

how about:
=LEFT(G8,FIND(":",G8)-1)-40
where G8 is that formula from my last post.

 

[jaugent27]

I just got a bunch of # signs. I extended the column out but still just a bunch of # signs. I tried both "enter" and "control+shift+enter"

 

[jaugent27]

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.

 

[jaugent27]

Do i need it to spit out hours in one column and minutes in the other since I cant have a negative time?