Nadine
New Member
- Joined
- May 12, 2020
- Messages
- 32
- Office Version
- 365
- Platform
- Windows
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".
Thank you and have a great day!
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".
VBA Code:
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!