custom format text to time

paul w · Dec 28, 2019

hello,
im not even sure if this is possible, on my time sheet I have to enter the hours used as eg. 38 hrs 00 mins.
ive managed to sort a custom format [hh] "hrs" mm "mins" to format. 38:00 to , "38 hrs 00 mins"
is there any way of formatting the cell so all i have to enter is 3800 without the colon and it would change to 38 hrs 00 mins,
the rest of my times are entered as 24hr so i use the formatting 00\:00 it is only this one section about 30 cells that payroll require it as hrs mins,
thanks in advance
paul

GlennUK · Dec 28, 2019

No, formatting can't do that for you. You could leave it as 3800, and use it in your calculations by scaling it down ( /2400 will do that) ... or you could do processing as recommended here:

Redirecting

llove · Dec 28, 2019

You can use flash fill to convert it also. Just type in the cell next to it 38 hrs 00 mins and flash fill the cells beside it and it will copy the format.

Yongle · Dec 28, 2019

Not sure if this is what you want

Column L is formatted using 00\:00 and numbers entered were 0500 1530 3600 3620 4750 15345

Formula in M1 copied down
=LEFT(L1,LEN(L1)-2)&" hrs "&RIGHT(L1,2)&" mins"

llove · Dec 28, 2019

llove said:
You can use flash fill to convert it also. Just type in the cell next to it 38 hrs 00 mins and flash fill the cells beside it and it will copy the format.

Rick Rothstein · Dec 28, 2019

paul w said:
is there any way of formatting the cell so all i have to enter is 3800 without the colon and it would change to 38 hrs 00 mins,

You could use this Custom Format BUT the value in the cell will NOT be a real time value (it will still be 3800)...

00" hrs "00" mins"

Akuini · Dec 28, 2019

Try this:
Assuming you use the custom format: [hh] "hrs" mm "mins"
Let's say the range in question is "A1:A10", you may change it in this part: If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

This is an Event Procedure, you need to put it in the code module of the sheet (say sheet1).
So copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.
Change Range("A1:A10") to suit.
The Sub Worksheet_Change is triggered whenever you exit a cell after you change its content.

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo SKIP:

If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Dim z
        Application.EnableEvents = False
        z = Target.Value
        If IsNumeric(z) Then
            If Len(z) = 4 Then
            Target.Value = Left(z, 2) & ":" & Right(z, 2)
            ElseIf Len(z) = 3 Then
            Target.Value = Left(z, 1) & ":" & Right(z, 2)
            End If
        End If
        Application.EnableEvents = True
        
    End If

Exit Sub
SKIP:
MsgBox "Error number " & Err.Number & " : " & Err.Description
Application.EnableEvents = True
End Sub

Note:
if you enter 2300 the result is: 23 hrs 00 mins
if you enter 230 the result is: 02 hrs 30 mins
if you enter 0230 the result is: 02 hrs 30 mins

Akuini · Dec 28, 2019

Sorry, the code in post #7 has a problem.
If you enter the time using ":" then for certain numbers it will generate wrong result, for example:
if you enter 12:00 the result is: 0:.5

so, replace this part: If IsNumeric(z) Then
with this: If IsNumeric(z) And InStr(z, ".") = 0 Then

DanteAmor · Dec 28, 2019

Here is another macro for you to consider.
The same idea of @Akuini with other variants, I hope this helps you.

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Not Intersect(Target, Range("B2:B20")) Is Nothing Then
    Dim h, m, s, wHour As Boolean
    Target.NumberFormat = "General"
    If IsNumeric(Target) Then
      If Target >= 1 And (Target - Int(Target) = 0) Then
        Select Case Len(Target.Value)
          Case 6
            h = Left(Target, 2)
            m = Mid(Target, 3, 2)
            s = Right(Target, 2)
          Case 5
            h = Left(Target, 1)
            m = Mid(Target, 2, 2)
            s = Right(Target, 2)
          Case 4
            h = Left(Target, 2)
            m = Right(Target, 2)
          Case 3
            h = Left(Target, 1)
            m = Right(Target, 2)
          Case 2
            h = Left(Target, 2)
            m = "00"
          Case 1
            h = Target
            m = "00"
        End Select
        If m < 60 And m >= 0 And m <> "" Then
          wHour = True
          Application.EnableEvents = False
          Target.Value = h & ":" & m
          Application.EnableEvents = True
        End If
      Else
        wHour = True
      End If
      If wHour Then
        Target.NumberFormat = "[hh]:mm"
      End If
    End If
  End If
End Sub

It works this way:
- Works in cells B2: B20
Examples:

Book1

B

C

1

Capture

Result

2

623526