Excel - zamień wpis liczby na wartość czasu

Niektóre komórki w programie Excel 2010 zostały sformatowane przy użyciu tego typu niestandardowego:

##":"##

ponieważ chcę, aby użytkownicy mogli wpisać „1345”, aby uzyskać „13:45” lub „923”, aby uzyskać „9:23”. To działa bez szału.

Moim problemem jest to, że w niektórych scenariuszach użytkownik może wpisać tylko „13”, co daje dziwny wynik, np. „-1”.

Czy możliwe jest sformatowanie komórek, aby format „13” został sformatowany na „13:00”, zachowując jednocześnie wspomniane formatowanie?

Dzięki.

Nie mogę odtworzyć „13” & gt; Scenariusz „-1”. Jakie są twoje ustawienia regionalne?

Ale Twój format niestandardowy zdecydowanie nie obsługuje czasów między północą a 1 w nocy. Wiodące zero w 013 zostaje usunięte.

Problem polega na tym, że nie wiesz, czy użytkownik, który wejdzie w 13, oznacza 00:13 lub 13:00. Tak długo, jak będzie to możliwe do interpretacji, żadne rozwiązanie formatowania, formuły lub kodu nie pomoże.

Chodzi bardziej o edukację użytkowników niż o cokolwiek innego.

Możesz zastosować VBA zamiast niestandardowych formatów, aby to zapewnić

a) użytkownik wprowadza wystarczającą liczbę znaków dla bez wątpienia interpretacji wartości czasu, np. 013 dla 0:13 i 1300 dla 13:00

b) wartości nie są po prostu sformatowane tak, aby wyglądały jak czasy, ale w rzeczywistości BĘDĄ wartościami czasu, które można wykorzystać w obliczeniach różnic czasowych

c) początkowe zera nie są usuwane po wprowadzeniu czasu.

Poniżej znajduje się UDF, który zamienia takie wpisy na wartości Date / Time. Zawiera również funkcję dodawania lub odejmowania dni przez dodanie jednego lub więcej znaków + lub - do wpisu. Wywołaj tę funkcję ze zdarzenia zmiany arkusza.

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