Number Format

[kstvns]

MS Excel 2003 with Win XP OS.

I have the following code for a Sign-In Sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Sheet2.Range("C7:C10,E7:E10,G7:G10,I7:I10,K7:K10,M7:M10,O7:O10")) Is Nothing Then
    With Target
        On Error Resume Next
        If IsNumeric(.Value) Then
            If .Value >= 2 Or .Value = 1 Then
                Application.EnableEvents = False
                .Value = Format(.Value, "00\:00")
                .NumberFormat = "[h]:mm"
                Application.EnableEvents = True
            End If
        End If
        On Error GoTo 0
    End With
End If
End Sub

Entering the times is a timesaver as a result of not having to enter the colon and am/pm.

If the Time Out time (5:00 p.m.) is less than the Time In time (8:00 a.m.), I have a problem (I get a -3 instead of a 9). It would be no problem if people were familiar with military time, but...

If possible, how do I modify the code so that the total hours for the day is calculated properly? I am also open to suggestions of a better way to accomplish this task.

 

[Lidsavr]

Try this for a number format. I tested it and it worked!

                .NumberFormat = "[h]:mm AM/PM"

 

[Jerry Sullivan]

If possible, how do I modify the code so that the total hours for the day is calculated properly? I am also open to suggestions of a better way to accomplish this task.

As Lidsavr's suggestion implies, the issue isn't so much getting the hours to calculate correctly, it is How do you get the correct AM/PM time entered and still allow the user to just type in "915" instead of 9:15 AM and "445" instead of 4:45 PM or 16:45?

The revision to:
.NumberFormat = "[h]:mm AM/PM"
would put in a default of AM for values less than 1200 and it would be pretty easy for a user to change the AM to PM if they typed in 400 and it incorrectly displayed 4:00 AM.

You could take that a step further in your code by adding 1200 to values that are likely PM values based your normal work hours. If your normal work hours are 8:00 AM to 5:00 PM then perhaps 700 to 1159 is AM and all other times are PM by default?