Military Time Conversion Plus a Formula

[clsmdh]

I have a spreadsheet that I need to create to track the login and logouts of phones in a call center. This spreadsheet is also used to track how log someone has been on lunch and break. I have over two hundred names to enter with a minimum of 8 in and out times. We only use military time so it is faster for me to key in the time without using the colon. I found a code to format the cell so I can just enter in numbers and it converts it to the military time including seconds, which is what i needed. The problem comes when i need to calculate the login and out time. I need to subtract the time out from the time in minus a 30 minute lunch break and be able to show if they were under 8 hours worked or over. By using the ##":"##":"## code it doesn't give me the right answer. So I am guessing I can't use this code to get this result. Does anyone have any suggestions? I barely know how to use VBA so unless it is a copy and paste, I'm usually lost using that, which is why the above formula was useful.

 

[rs2k]

It sounds something very similar to what I use, can you eleborate on 'a minimum of 8 in and out times'. Is this saying the phones are logged in & out 8 times a day?

From the time they are logged out, to the time they are logged back in, is this the time you want to capture?

Or, is it from login in the morning to log out in the evening plus a 30 minute break?

 

[clsmdh]

i have a cell that represents log in time, the next cell represent the logout of the first break, the next is the login from that break. the cell after that represents the total time of the break. The same thing happens for the next few groups of cells. there are a minimum of two breaks and one lunch. then the cell represents the logout time for the day. the cell after that needs to be the difference between the initial log in time and the final logout time minus the 30 minute lunch. I do have a column after that that shows if they were over or under 8 hours. All of the times are military including seconds. I can get the formulas if I use the time format and enter the colon. As I stated, I need to enter in all login/out times everyday, which is why using the colon slows me down. I need to figure out how to get the differences in times with a formula, but not having to initially enter the time in with a colon. Below are the columns I use.

<TABLE style="WIDTH: 607pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=799 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" span=2 width=50><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1393" width=49><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" span=2 width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" span=2 width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" span=2 width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" span=2 width=50><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1422" width=50><TBODY><TR style="HEIGHT: 39.75pt" height=53><TD class=xl68 id=ctl00_MasterPageContainerCellLeft style="BORDER-RIGHT: #1f497d 1.5pt solid; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #1f497d 1.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; HEIGHT: 39.75pt; BACKGROUND-COLOR: transparent" width=50 height=53>Login</TD><TD class=xl69 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #c0c0c0; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Break
Out</TD><TD class=xl77 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 37pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=49>Break
In</TD><TD class=xl71 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>+/-
15 min.</TD><TD class=xl72 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #1f497d 1.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Lunch
Out</TD><TD class=xl70 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Lunch
In</TD><TD class=xl73 style="BORDER-RIGHT: #17375d 1.5pt solid; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>+/-
30 min.</TD><TD class=xl69 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #c0c0c0; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Break
Out</TD><TD class=xl70 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Break
In</TD><TD class=xl71 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>+/-
15 min.</TD><TD class=xl72 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #1f497d 1.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Break
Out</TD><TD class=xl70 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Break
In</TD><TD class=xl71 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>+/-
15 min.</TD><TD class=xl68 style="BORDER-RIGHT: #1f497d 1.5pt solid; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #1f497d 1.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Logout</TD><TD class=xl69 style="BORDER-RIGHT: #d8d8d8 0.5pt dashed; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #c0c0c0; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>Total
Hours</TD><TD class=xl74 style="BORDER-RIGHT: #1f497d 1.5pt solid; BORDER-TOP: #1f497d 1.5pt solid; BORDER-LEFT: #d8d8d8; WIDTH: 38pt; BORDER-BOTTOM: #1f497d 1.5pt solid; BACKGROUND-COLOR: transparent" width=50>+/- 8
hours</TD></TR></TBODY></TABLE>

 

[rs2k]

I can get it to work, but I have done a slight restructure of your table. I am guessing you have used the same code as me for inputting times (to avoid typing the colon all the time;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C2:L30")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

I have moved the sub-totals (+/- 15/30 mins etc...) column to the end and adjusted the above code to cater for time input across the break in/out, lunch in/out times. I then totalled the amount of breaks (='break in - break out') and the same for time worked (='break out - break in')

So the layout I used is (sorry works laptop can't upload piccy)

Row 2 has the headings, Col A = Day, Col B = Date, Col C= Login Col D=Break out, Col E = break in, Col F= Lunch out, Col G= Lunch in etc... Col M is the total for breaks and Col N is the total for Time worked.

So in Col M I have =SUM(E3-D3)+(G3-F3)+(I3-H3)+(K3-J3)
and Col N I have =SUM(D3-C3)+(F3-E3)+(H3-G3)+(J3-I3)+(L3-K3)

If any time (in or out) is not filled you will get an ####### error, this can be worked around within the SUM formula.

If this is unclear, PM me and I will send you the file.

HTH
Colin