Formula help for timesheet?

[bmorris]

I am looking for formula and formatting to make entering a time in and a time out super simple as well at the formula for totals! Please help!

Time In	Time Out	Total Hours
8:00	12:30	4.50
1:00	8:15	7.25

<colgroup><col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;" span="2" width="89"> <col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3896;" width="110"> <tbody>
</tbody>

 

[bmorris]

Good Morning!
I entered in custom hh:mm and this is the result:

Time In	Time Out	Total Hours
00:00	00:00

<colgroup><col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;" span="2" width="89"> <col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3896;" width="110"> <tbody>
</tbody>

 

[JoeMo]

Good Morning!
I entered in custom hh:mm and this is the result:

Time In	Time Out	Total Hours
00:00	00:00

<tbody>
</tbody>

What do you get if you type 8:00 in time in and 10:00 in time out?

 

[bmorris]

That screen shot is the result when I tyup 8:00 and 10:00.
Thank you for your help with this. For now, I am going to allow my users to write in a time until I can get this figured out.
If you have any other ideas, I would appreciate your help!

 

[bjbalmforth]

If you use the formula as provide by JoeMo in column C as advised, you can use this bit of code to put : in between the numbers inserted in cells in Column A and B ie: if you type 1200 in cell A2 then it will become 12:00 so the user will not have to enter it as a time just a number.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput, NewInput
'Adjust Ranges To Suit
If Not Intersect(Target, Me.Range("A1:A20,B1:B25")) Is Nothing Then

UserInput = Target.Value
If UserInput > 0 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)

Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub

Hope that helps

 

[bmorris]

Hi BJ,
Where do I insert this "code"? This is really cool, I appreciate your help with this......but I am a bit challenged when it comes to this degree of excel!!

 

[bjbalmforth]

Just right click on the sheet tab and copy and paste code into sheet module, also try this formula in column C =B1+(A1>B1)-A1 and copy down to calculate the hours worked. Format the cells in column C as h:mm and you will get the correct hours worked.

 

[bmorris]

We are getting really close with this!!! Ok....this is what I have now after doing all this you recommended: Did I do something wrong?

Day	Date	Job	Cost Code	Time In	Time Out	Total Hours
Saturday	4/26/14			8:00	10:00	2:00
Saturday	4/26/14			11:00	3:00	##########
Saturday	4/26/14			4:00	8:00	4:00
Sunday	4/27/14					0:00
Sunday	4/27/14					0:00
Sunday	4/27/14					0:00
Monday	4/28/14			7:30	11:00	3:30
Monday	4/28/14			12:00	6:30	##########
Monday	4/28/14					0:00

<colgroup><col style="width: 105pt; mso-width-source: userset; mso-width-alt: 4977;" width="140"><col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3157;" width="89"><col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3783;" width="106"><col style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;" span="3" width="108"><col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;" width="82"><tbody>
</tbody>

 

[bjbalmforth]

Not sure why you are getting # in your total hours column, My workbook does not do this, I have the columns, time in and time out formatted as text and the total hours column formated as h:mm in custom formating.

If you want the total hours column to be blank unless a time is entered for an employee try this =IF(B1="","",B1+(A1>B1)-A1) adjust range to suit.

Hope that helps.

 

[bmorris]

I tried this again in a new spreadsheet and I get the same result when I input times that cross of the "lunch hour". I guess this timesheet isn't as easy as I had hoped it would be! Thank you for all your help!

 

[bjbalmforth]

What do you mean cross over the lunch hour?