Excel: macro for automatic time formatting (no date)

Question

I am looking for a simple macro to specify cells as type "time", and inputting only numbers (for example, "955") will format this (and recognize how) "hh: mm". In other words, by typing "955", the cell interprets the value "09:55", while all I can do is right now interpret it as "1902-08-12 00:00:00" (most likely 955 is the day number for 1902-08-12).

EDIT:

Part of the need for this is to enable the calculation of time after entering text (for example, subtract two time values ​​to obtain a time interval or add several time series to get the total).

Answer 1

VBA Code:

Dim val As String
val = myCell.Value
Dim newval As String
If Len(val) = 2 Then
    newval = "00:" & val
ElseIf Len(val) = 3 Then
    newval = "0" & Left(val, 1) & ":" & Right(val, 2)
ElseIf Len(val) = 4 Then
    newval = Left(val, 2) & ":" & Right(val, 2)
Else
    newval = "Not a valid time"
End If
myCell.Value = newval

This code does not determine if the last two digits are a valid time (more than 59 minutes), but otherwise it should handle most cases.

You will also need to add the case if someone enters a digit in 1, i.e. 1 9 minutes after midnight.

---

If you want it to be formatted as actual time (date-time type), change the cell formatting to hh: mm. The value entered, for example, 955, must be processed to obtain a fraction of the day.

pseudo code:

(left(myCell,len(myCell)-2) + (right(myCell,2)/60)) / 24

This gives the correct decimal value for how many days have passed and, thus, will be displayed as the corresponding "time" in the cell with formatting hh: mm.

Answer 2

VBA, , . VBA, , .

=TIMEVALUE(IF(LEN(A5)=3,"0"&LEFT(A5,1)&":",LEFT(A5,2)&":")&RIGHT(A5,2))

( A5 - , 955)

( , .)

, . .

Answer 3

- Excel ":" . , "-", : ## - ##
, 24 .

, ( , , ). script, .