Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
Thanks for sharing your input. My Excel is UK and my regional setting is Denmark(Danish). If you have a macro at hand I would be grateful. –  Morten Laustsen Jun 18 '13 at 12:40
    
It's going on 1:00 am where I live. If nobody else steps in, I'll post my suggestion in the morning. –  teylyn Jun 18 '13 at 12:43
    
I've added some code to the answer above. –  teylyn Jun 18 '13 at 21:33
    
Thank you - works like a charm. Much appreciated. –  Morten Laustsen Jun 19 '13 at 5:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.