Macro: Replace number with time, interpret AM/PM

[MartinS13X]

Dear MrExcel experts,

I'm trying to save my girlfriend, who is a manager at a warehouse for a non-profit organisation, some time with filling out the time sheets/logs for staff and volunteers.

She's not very good at converting AM/PM to 24H format which would basically fix our little "problem".
Excel is fine when you're in AM territory. (ie. 7:00 means 7:00 AM). Times in the afternoon are a bit more tricky, as, as far as I know, it isn't possible to format cells purely for PM. Requiring to type 3:00 p (or PM), which Excel then will interpret as 3:00 PM.

So, in my quest to speed up things I figured it should be possible for her to enter a "simple" number (Table row 3) and then have excel convert it to the proper time (table row 4).

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Monday [/TD]
[TD="align: center"]Monday
[/TD]
[TD="align: center"]Tuesday [/TD]
[TD="align: center"]Tuesday [/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]Friday [/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]Week Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"]In[/TD]
[TD="align: center"]Out[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]715[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]38:15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7:15 AM [/TD]
[TD="align: center"]3:00 PM[/TD]
[TD="align: center"]7:00 AM[/TD]
[TD="align: center"]3:00 PM[/TD]
[TD="align: center"]7:00 AM[/TD]
[TD="align: center"]3:00 PM[/TD]
[TD="align: center"]7:00 AM[/TD]
[TD="align: center"]3:00 PM[/TD]
[TD="align: center"]7:00 PM[/TD]
[TD="align: center"]2:30 PM[/TD]
[TD="align: center"]38:15[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I found a macro on the WWW which does basically exactly what I want it to do, except it still doesn't make a distinction between AM and PM (when time isn't entered in 24H format). My macro skills are limited, usually I'm able to figure stuff out though and alter/edit so they work for me, but now it has me a bit stumped.

The weird thing is that the macro as is, seems to be working for the whole sheet instead of the range. Even when I change it to say ("C6:C28") it still changes times in other cells as well. I don't get it...

My end goal is to add multiple ranges, so time entered into columns:
C, E, G, I, K will be interpreted as AM
D, F, H, J, L will be interpreted as PM

Thanks in advance for any help.

Here is the macro:

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


On Error GoTo EndMacro
If Application.Intersect(Target, Range("C6:L28")) Is Nothing Then
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 3
            TimeStr = Left(.Value, 1) & ":" & _
            Right(.Value, 2)
        Case 4
            TimeStr = Left(.Value, 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

 

[MartinS13X]

I am not allowed to edit my post (bit of a weird thing in my opinion, but anyways)... a little correction:

"[...]I figured it should be possible for her to enter a "simple" number (Table row 3) and then have excel convert it to the proper time (table row 4)."

Should be:

"I figured it should be possible for her to enter a "simple" number (See Table row 3) and then have excel convert it to the proper time (See Table row 4)."

In other words, when she enters 700 in cell C6, the macro changes it to 7:00 AM and when she enters 2:45 in Cell D6, the macro changes it to 2:45PM

Thanks again for any suggestions.

 

[My Aswer Is This]

Please explain to me what the user will enter and what you want the results to be.

Are the users suppose to enter current time into the cell when they check in?

And then enter current time when they check out?

And then you want time worked automatically entered into another cell?

 

[MartinS13X]

Please explain to me what the user will enter and what you want the results to be.

Are the users suppose to enter current time into the cell when they check in?

And then enter current time when they check out?

And then you want time worked automatically entered into another cell?

The sheet/page is printed out and stuck on a board, where staff write the times on it. at the end of the week, my gf enters the time into the excel sheet.

The macro is supposed to change the value of the cell it is entered in.
So if (for example) 725 is entered into cell C6, the macro changes it to 7:25 AM. If she enters 315 in cell D6, the macro changes it to 3:15 PM

 

[My Aswer Is This]

So after all these values are entered is she just going to look at it or try to get formulas to come up with hours worked?

 

[Phuoc]

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Temp As Variant
If Not Application.Intersect(Target, Range("C6:L28")) Is Nothing Then
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.HasFormula = True Then Exit Sub
Application.EnableEvents = False
With Target
    Temp = .Value
        If IsNumeric(Temp) = True Then
                If Temp >= 700 Then
                        .Value = TimeSerial(Int(Temp / 100), Temp Mod 100, 0)
                Else
                        .Value = TimeSerial(Int(Temp / 100), Temp Mod 100, 0) + 0.5
                End If
                .NumberFormat = "hh:mm AM/PM"
        Else
          MsgBox "You did not enter a valid time"
End If
End With
Application.EnableEvents = True
End If
End Sub

 

[Michael M]

I am not allowed to edit my post (bit of a weird thing in my opinion, but anyways)

You are, but it must be within the 5 minute window.....reason for this is that someone could have answered the post and you may have decided to change your request....and the post ends up in a crazy loop of you and the OP not being synched together.

Maybe change these lines to exit sub

On Error GoTo EndMacro
If Application.Intersect(Target, Range("C6:L28")) Is Nothing Then
Exit Sub
End If

 

[MartinS13X]

So after all these values are entered is she just going to look at it or try to get formulas to come up with hours worked?

In column M, the hours are added up to give a week total.
That's fairly simple to do and i didn't mention it in the post as i didn't want to complicate my story any further.

 

[My Aswer Is This]

I asked the question because if you just want to look at the results it my be easy.
But if you want to total up times it would be more difficult.

I know of no way to do what you want.
I would suggest entering times correctly like 9:25

Entering a value like 925 and converting it to a time value is not something I know how to do.

 

[MartinS13X]

Thanks all. I'm a bit closer to my solution.
I've got it working for the "In" times.
"All" I have to do now is figure out how to get to the PM times...

This is where I am now.

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


On Error GoTo EndMacro
 Dim r1, r2, r3, r4, r5, MultiRange As Range
 Set r1 = Sheets("Sheet1").Range("C6:C28")
 Set r2 = Sheets("Sheet1").Range("E6:E28")
 Set r3 = Sheets("Sheet1").Range("G6:G28")
 Set r4 = Sheets("Sheet1").Range("I6:I28")
 Set r5 = Sheets("Sheet1").Range("K6:K28")
 Set MultiRange = Union(r1, r2, r3, r4, r5)
If Application.Intersect(Target, MultiRange) 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 3
            TimeStr = Left(.Value, 1) & ":" & _
            Right(.Value, 2)
        Case 4
            TimeStr = Left(.Value, 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 was thinking the easiest solution was to create another "union", but then for the "out" times and add a function which adds 1200 to the values entered in the cells before they are formatted to time.