Excel - преобразовать ввод числа в значение времени

Question

Я отформатировал некоторые ячейки в Excel 2010 с этим пользовательским типом:

##":"##

потому что я хочу, чтобы пользователи могли набирать "1345", чтобы получить «13:45» или "923", чтобы получить «9:23». Это работает без шума.

Моя проблема в том, что в определенных сценариях пользователь может набирать только "13", и это дает странный результат, например «-1».

Можно ли отформатировать ячейки так, чтобы "13" было отформатировано до «13:00», при этом сохраняя форматирование, упомянутое выше?

Благодарю.

Answer 1

Я не могу воссоздать сценарий "13"> "-1". Каковы ваши региональные настройки?

Но ваш пользовательский формат определенно не обрабатывает время между полуночью и 1 часом ночи. Ведущий ноль в 013 удаляется.

Проблема в том, что вы не знаете, означает ли пользователь, который вводит 13, 00:13 или 13:00. Пока это открыто для интерпретации, никакое форматирование, формула или решение кода не помогут.

Это больше касается обучения пользователей, чем чего-либо еще.

Возможно, вы захотите применить VBA вместо пользовательских форматов, чтобы

а) пользователь вводит достаточно символов для безошибочной интерпретации значения времени, например, 013 для 0:13 и 1300 для 13:00

b) значения не просто отформатированы, чтобы выглядеть как времена, но на самом деле будут значения времени, которые могут использоваться в расчетах для разницы во времени

в) начальные нули не удаляются при вводе времени.

Ниже приведен UDF, который превращает такие записи в значения даты / времени. Он также содержит функцию добавления или вычитания дней путем добавления одного или нескольких знаков + или - к записи. Вызовите эту функцию из события изменения рабочего листа.

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