Excel - convert number entry into time value

I have formatted some cells in Excel 2010 with this custom type:

##":"##

because I want the users to be able to type "1345" to get "13:45" or "923" to get "9:23". This works without hazzle.

My problem is that in certain scenarios the user might only type "13" and this gives some weird result like "-1".

Is it possible to format the cells so "13" would be formatted to "13:00" while still maintaining the formatting mentioned above?

Thanks.

Answer

I cannot recreate the "13" > "-1" scenario. What are your regional settings?

But your custom format definitely does not handle times between midnight and 1 am. The leading zero in 013 gets stripped out.

The problem with this is that you don't know if the user who enters 13 means 00:13 or 13:00. As long as this is open to interpretation, no formatting, formula or code solution will help.

This is more about user education than about anything else.

You may want to apply VBA instead of custom formats to ensure that

a) the user enters enough characters for a doubt-free interpretation of a time value, e.g. 013 for 0:13 and 1300 for 13:00

b) the values are not just formatted to look like times but will actually BE time values that can be used in calculations for time differences

c) leading zeros are not stripped when a time is entered.

Below is a UDF that turns such entries into Date/Time values. It also contains the feature of adding or subtracting days by adding one or more + or - signs to the entry. Call this function from a worksheet change event.

Public Function TimeEntry(iTarget As String) As Variant

' convert values into date/times
'
' expected user input in the format of
'
' 1430      will be converted to today, 14:30
' 1430+     will be converted to today + 1 day, 14:30
' 1430-     will be converted to today - 1 day, 14:30
'
' multiple + or - signs are allowed to enable quick entry of time several days ago or
' in the future
'

Dim IncDay As Integer, DecDay As Integer
Dim eTime As Variant
On Error GoTo Message
    Msg = ""
    eTime = Application.WorksheetFunction.Substitute(iTarget, "+", "")
    eTime = Application.WorksheetFunction.Substitute(eTime, "-", "")
    eTime = Format(eTime, "0000")
' a few error checks to validate the data
' - can only start with a number
' - must be a number after stripping off the + and - signs
' - cannot be less than 3 or more than 4 digits
' - cannot be more than 23:59
    If Not IsNumeric(Left(iTarget, 1)) Or _
        Not IsNumeric(eTime) Or _
        Len(eTime) > 4 Or _
        eTime > 2359 Then
        GoTo Message
    End If
' insert a colon before the last two digits and convert into a time
    eTime = Left(eTime, Len(eTime) - 2) & ":" & Right(eTime, 2)
    eTime = TimeValue(eTime)
' determine how many days to increase or decrease
    IncDay = Len(iTarget) - Len(Application.WorksheetFunction.Substitute(iTarget, "+", ""))
    DecDay = Len(iTarget) - Len(Application.WorksheetFunction.Substitute(iTarget, "-", ""))

' increase/decrease current date and add the time value
    TimeEntry = Date + IncDay + (DecDay * -1) + eTime

GoTo Ende
Message:
        Msg = "Invalid time value entered" & Chr(10) & Chr(10)
        Msg = Msg & "Please enter time values like this: " & Chr(10) & Chr(10)
        Msg = Msg & " 900   for 9:00 am today " & Chr(10)
        Msg = Msg & "2130+  for 21:30 tomorrow " & Chr(10)
        Msg = Msg & " 000+  for midnight tonight" & Chr(10)
        Msg = Msg & "1000-- for 10 am two days ago."
        MsgBox Msg
        TimeEntry = ""

Ende:
End Function