Excel VBA time issue

Question

Thanks for looking,

i have an issue with the below VBA code. Probably something simple.

If i enter the time as 23:30 it will auto change to 23:29

If i enter as 2330 it changes to 23:30 as it should.

so why when i enter 23:30 does it change to 23:29 ? it should remain as 23:30

Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim xStr As String
    Dim xVal As String
    Set rng1 = Range("J:J")
    Set rng2 = Range("P:P")
    Set rng3 = Range("S:S")
        On Error GoTo EndMacro
    If Application.Intersect(Target, Union(rng1, rng2, rng3)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.Row < 5 Then Exit Sub
    Application.EnableEvents = False
    With Target
        If Not .HasFormula Then
            Target.Value = Replace(Target.Value, ";", ":")
            Target.Value = Left(Target.Value, 5)
            xVal = .Value
            Select Case Len(xVal)
                Case 1 ' e.g., 1 = 00:01 AM
                    xStr = "00:0" & xVal
                Case 2 ' e.g., 12 = 00:12 AM
                    xStr = "00:" & xVal
                Case 3 ' e.g., 735 = 07:35 AM
                    xStr = "0" & Left(xVal, 1) & ":" & Right(xVal, 2)
                Case 4 ' e.g., 1234 = 12:34
                    xStr = Left(xVal, 2) & ":" & Right(xVal, 2)
                Case 5 ' e.g., 12:45 = 12:45
                    xStr = Left(xVal, 2) & Mid(xVal, 2, 1) & Right(xVal, 2)
                Case Else
                    Err.Raise 0
            End Select
            .Value = Format(TimeValue(xStr), "hh:mm")
        End If
    End With
    Application.EnableEvents = True
    Exit Sub
EndMacro:
    Application.EnableEvents = True
End Sub

Answer 1

This is because Excel treats the time fields as fractions of a day.

For example:

• 24 hours = 1
• 12 hours = 0.5
• 6 hours = 0.25

In your case: 23h30 = 0,979166666...

But you only get the first 5 characters: Left(Target.Value, 5) ' "0.979"

And pass this new value (0.979) as the current value to the cell Target.Value = Left(Target.Value, 5).

So on line xStr = Left(xVal, 2) & Mid(xVal, 2, 1) & Right(xVal, 2), what your code is actually doing is:

Left(0.979, 2) ' -> "0."

Mid(xVal, 2, 1) ' -> "."

Right(xVal, 2) ' -> "79"

So when you join, the result is as follows:

"0." & "." & "79" = "0..79"

And "0..79" is not a valid value for the cell. Then an error occurs and the function ends up holding the value of 0.979 ~ 23:29.

My suggestion, do not change the value of the cell if it is already smaller than one. Because it indicates that it is already a time.

1. Delete the line: Target.Value = Left(Target.Value, 5)
2. Assign the value of xval directly: xVal = Left(Target.Value, 5)
3. Alter the "case 5" condition as follow: xStr = Format(Target.Value, "hh:mm")

The complete code looks like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim xStr As String
    Dim xVal As String
    Set rng1 = Range("J:J")
    Set rng2 = Range("P:P")
    Set rng3 = Range("S:S")
    On Error GoTo EndMacro

    If Application.Intersect(Target, Union(rng1, rng2, rng3)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.Row < 5 Then Exit Sub
    Application.EnableEvents = False
    With Target
        If Not .HasFormula Then
            Target.Value = Replace(Target.Value, ";", ":")
            xVal = Left(Target.Value, 5) ' CHANGED!
            Select Case Len(xVal)
                Case 1 ' e.g., 1 = 00:01 AM
                    xStr = "00:0" & xVal
                Case 2 ' e.g., 12 = 00:12 AM
                    xStr = "00:" & xVal
                Case 3 ' e.g., 735 = 07:35 AM
                    xStr = "0" & Left(xVal, 1) & ":" & Right(xVal, 2)
                Case 4 ' e.g., 1234 = 12:34
                    xStr = Left(xVal, 2) & ":" & Right(xVal, 2)
                Case 5 ' e.g., 12:45 = 12:45
                    xStr = Format(Target.Value, "hh:mm") ' CHANGED!
                Case Else
                    Err.Raise 0
            End Select
            .Value = Format(TimeValue(xStr), "hh:mm") ' CHANGED!
        End If
    End With
    Application.EnableEvents = True
    Exit Sub
EndMacro:
    Application.EnableEvents = True
End Sub

Answer 2

It seems that you are trying to do things with dates the wrong way, since the usual and recommended way to format dates is to use the Format statement.

However, if you want to manipulate the dates/times as strings, you need to do a few changes to your code, the 3 lines just after the test:

   If Not .HasFormula Then
        .Value = Replace(.Text, ";", ":") ' <-- use .Text
        .Value = Left(.Text, 5)           ' <-- 
        xVal = .Text                      ' <--

Or better yet, assign directly xVal:

   If Not .HasFormula Then
        xVal = Left(Replace(.Text, ";", ":"), 5) ' <--

On the other hand, what you were getting with .Value was an encoded date, which is usually encoded as a number, and then you were truncating the date to the leftmost 5 digits, which naturally leads to some imprecision.