Worksheet Change Event - Convert Integer to 24hr Time Format

[Nadine]

Hello and thank you for any attention my post may receive.

In my range C5:AP5 I enter an integer as a time representation. However I would like the integer to automatically change to hh:mm format on cell exit.

I am unsure as to how to put this piece of code, courtesy of Allan Wyatt, into a 'Workhseet-Change'. I will to change Allan's format to "hh:mm".

Sub NumberToTime()
    Dim rCell As Range
    Dim iHours As Integer
    Dim iMins As Integer

    For Each rCell In Selection
        If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then
            iHours = rCell.Value \ 100
            iMins = rCell.Value Mod 100
            rCell.Value = (iHours + iMins / 60) / 24
            rCell.NumberFormat = "h:mm AM/PM"
        End If
    Next
End Sub

Thank you and have a great day!

 

[Zot]

For change in any sheet in workbook range C5:AP5. Otherwise if for any specific sheet under Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim iHours As Integer, iMins As Integer

Application.EnableEvents = False
If Not Intersect(Target, Range("C5:AP5")) Then
    If IsNumeric(Target.Value) And Len(Target.Value) > 0 Then
        iHours = Target.Value \ 100
        iMins = Target.Value Mod 100
        Target.Value = (iHours + iMins / 60) / 24
        Target.NumberFormat = "hh:mm AM/PM"
    End If
End If
Application.EnableEvents = True

End Sub

 

[Trebor76]

Hi Nadine,

Follow these five steps to put the below code as an event macro on the sheet in question:

1. Copy the code below to the clipboard (Ctrl + C)
2. Right click on the tab you want the macro to run and from the shortcut menu select View Code
3. Paste (Ctrl + V) the code from step 1
4. From the File menu Select Close and Return to Microsoft Excel
5. Test the code works by editing a cell within the range C5:AP5 while on the sheet in question.

Regards,

Robert

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim iHours As Integer
    Dim iMins  As Integer

    If Not Intersect(Target, Range("C5:AP5")) Is Nothing Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            If IsNumeric(Target.Value) And Len(Target.Value) > 0 Then
                iHours = Target.Value \ 100
                iMins = Target.Value Mod 100
                Target.Value = (iHours + iMins / 60) / 24
                Target.NumberFormat = "hh:mm"
            End If
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If

End Sub

 

[Zot]

Looking at the @Trebor76 code above I realized that I forget to put Is Nothing ? and the ScreenUpdating is also good to have. Thanks to @Trebor76

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iHours As Integer, iMins As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Range("C5:AP5")) Is Nothing Then
    If IsNumeric(Target.Value) And Len(Target.Value) > 0 Then
        iHours = Target.Value \ 100
        iMins = Target.Value Mod 100
        Target.Value = (iHours + iMins / 60) / 24
        Target.NumberFormat = "hh:mm AM/PM"
    End If
End If
Application.EnableEvents = True

End Sub

 

[Trebor76]

Thanks to @Trebor76

Thank you

 

[mikerickson]

There is a problem. I put 1134 in C5 and the routine converted that number to a time of 11:34.
I then realized my mistake and corrected the cell to 11:35, by changing one digit in the cell, and the routine converted that number to 0:00.
You may want to add a Target.Value > 99 test before converting to a time value.

 

[Nadine]

Hi Nadine,

Follow these five steps to put the below code as an event macro on the sheet in question:

1. Copy the code below to the clipboard (Ctrl + C)
2. Right click on the tab you want the macro to run and from the shortcut menu select View Code
3. Paste (Ctrl + V) the code from step 1
4. From the File menu Select Close and Return to Microsoft Excel
5. Test the code works by editing a cell within the range C5:AP5 while on the sheet in question.

Regards,

Robert

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim iHours As Integer
    Dim iMins  As Integer

    If Not Intersect(Target, Range("C5:AP5")) Is Nothing Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            If IsNumeric(Target.Value) And Len(Target.Value) > 0 Then
                iHours = Target.Value \ 100
                iMins = Target.Value Mod 100
                Target.Value = (iHours + iMins / 60) / 24
                Target.NumberFormat = "hh:mm"
            End If
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If

End Sub

There is a problem. I put 1134 in C5 and the routine converted that number to a time of 11:34.
I then realized my mistake and corrected the cell to 11:35, by changing one digit in the cell, and the routine converted that number to 0:00.
You may want to add a Target.Value > 99 test before converting to a time value.

Thank you for your time to test and reply Mike.

I have added your suggestion.

 

[Nadine]

Thank you

Hi Nadine,

Follow these five steps to put the below code as an event macro on the sheet in question:

1. Copy the code below to the clipboard (Ctrl + C)
2. Right click on the tab you want the macro to run and from the shortcut menu select View Code
3. Paste (Ctrl + V) the code from step 1
4. From the File menu Select Close and Return to Microsoft Excel
5. Test the code works by editing a cell within the range C5:AP5 while on the sheet in question.

Regards,

Robert

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim iHours As Integer
    Dim iMins  As Integer

    If Not Intersect(Target, Range("C5:AP5")) Is Nothing Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            If IsNumeric(Target.Value) And Len(Target.Value) > 0 Then
                iHours = Target.Value \ 100
                iMins = Target.Value Mod 100
                Target.Value = (iHours + iMins / 60) / 24
                Target.NumberFormat = "hh:mm"
            End If
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If

End Sub

Thank you very much Trebor. Greatly appreciate your time and help.

Have a great dyay!

 

[Nadine]

Looking at the @Trebor76 code above I realized that I forget to put Is Nothing ? and the ScreenUpdating is also good to have. Thanks to @Trebor76

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iHours As Integer, iMins As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Range("C5:AP5")) Is Nothing Then
    If IsNumeric(Target.Value) And Len(Target.Value) > 0 Then
        iHours = Target.Value \ 100
        iMins = Target.Value Mod 100
        Target.Value = (iHours + iMins / 60) / 24
        Target.NumberFormat = "hh:mm AM/PM"
    End If
End If
Application.EnableEvents = True

End Sub

Thank you for the pick-up and suggestion Zot.

 

[Trebor76]

Thank you very much Trebor. Greatly appreciate your time and help.

You're welcome