Convert cell to time if entered as decimal

[steve.waye]

Hi all,

I've searched these forums for a solution to this problem but I'm thinking it might not be a simple answer.

I currently have a range of cells in a table that I want to display as time. I have used "Format Cells" and applied the custom format [h]:mm which is working fine.

This however requires the user to enter the time with a colon which slows down data entry.

For example, for 1 hour they need to enter "1:" for the correct result. If they simply enter a "1" the cell reverts to 24:00 (I assume it interprets it as 1 day).

I want users to be able to enter times in either regular time format OR in decimal format and have the result displayed in time format [h]:mm.

i.e. 2 hours 30 minutes can be entered as "2:30" or as "2.5" and the displayed result will be "2:30". Entering an integer should give a result in hours (entering "2" will give "2:00).

I assume this will require some VBA which I'm only a novice at.

Thanks in advance.

 

[Jeffrey Mahoney]

This is a routine I created some time ago. It works for me. You could put code for the time conversion into the worksheet change sub, but I like to keep them separate.

This needs to be pasted into the SHEET level module. This allows the cell to be tested after a user puts a time in.

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo NoTimeConv
  If Not Intersect(Range("TimeArea"), Target) Is Nothing Then
    Call TimeConversion(Target)
  End If

NoTimeConv:

End Sub

This can get pasted into a standard module. TimeArea is a named range. You can use any range you need. The Sub tests the length to determine if they put in minutes and seconds. Time is entered as 112415 for 11:24:15. or 816 for 08:16.

Sub TimeConversion(ByVal Target As Range)
  Dim i As Range
  Dim cel As Range
  Dim A As String
  Dim B As String

  Set i = Intersect(Target, Range("TimeArea"))
  If Not i Is Nothing Then
    Application.EnableEvents = False
    For Each cel In i
     
      If cel.Value > 0.99999999 Then
        A = cel.Value
        B = A
        Select Case Len(A)
          Case 6
            B = Left(A, 2) & ":" & Mid(A, 3, 2) & ":" & Mid(A, 5)
          Case 5
            B = "0" & Left(A, 1) & ":" & Mid(A, 2, 2) & ":" & Mid(A, 4)
          Case 4
            B = Left(A, 2) & ":" & Mid(A, 3, 2)
          Case 3
            B = "0" & Left(A, 1) & ":" & Mid(A, 2, 2)
          Case 2
            B = "00" & ":" & A
        End Select
        cel.Value = B
      End If
    Next cel
    Application.EnableEvents = True
  End If


End Sub

 

[steve.waye]

Code entered and changed "TimeArea" in two places to named range "Hours_Worked".

Tested as follows...

Entering "2" results in "48:00"
Entering "2.5" results in "02:.5"

I removed cell formatting and tested again...

Entering "2" results in "2"
Entering "2.5" results in "02:.5"

So no joy unfortunately.

 

[Jeffrey Mahoney]

Enter in 0200 to get 02:00

Looks like my code would convert 2 to 6 numbers into a time. A single digit is not accepted at this point. You have a good start. How do you want it to be?

 

[steve.waye]

As per original post...

• Entering "2" will give "2:00".
• Entering "2:30" will give "2:30".
• Entering "2.5" will give "2:30".

If the conversion from decimal to time is problematic, then it would be acceptable to simply use a decimal point instead of a colon when entering time...

• Entering "2" will give "2:00".
• Entering "2:30" will give "2:30".
• Entering "2.30" will give "2:30".

I just want to avoid getting "48:00" when "2" is entered, and also to simplify data entry by using a dot instead of a colon.

Just to clarify, the data being entered is a specific number of hours per cell, and the individual values in the column will be summed (hence the use of format [h]:mm).

 

[steve.waye]

Enter in 0200 to get 02:00

Looks like my code would convert 2 to 6 numbers into a time. A single digit is not accepted at this point. You have a good start. How do you want it to be?

But your code is a great start, so thanks.