Excel: macro to auto-format time (without date)

Question

I'm looking for a simple macro to specify cells as being of type "time" and typing only numbers (for example "955") will format that (and recognise as) "hh:mm" time. In other words, by typing "955", the cell would interpret to "09:55", whereas all I can have it do right now is interpret to "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 allow calculation of times post typing (for example, substract two time values to get a timespan, or add several timespans to get a total).

Answer 1

For some reason Excel does not allow to use ":" in your custom format. But if you OK with another delimiter, say dash "-", then you can simply create a custom format like this: ##-##
Of course your time has to be in 24 hours format.

Alternatively, you may first enter all your times just like numbers (or better as text if you don't want to lose trailing zeros). And then run your script to insert semicolons between hours and minutes.

Answer 2

This isn't the VBA, but a formula that will change the formatting. You could incorporate this into VBA if you wanted to, but should help as a jumping point.

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

(If A5 is the cell where you enter 955)

(Also, make sure to format the formula cell as your desired time formatting.)

I can help with the code if needed. Just post back.

Answer 3

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 detect if the last two digits are a valid time (greater than 59 minutes), but otherwise it should handle most cases.

You'll also need to add a case if someone types in 1 digit, ie. 1 thru 9 minutes after midnight.

---

If you want it to be formatted as an actual time (date-time type), then change the formatting of the cell to hh:mm. The value typed in, for example 955, must be manipulated to produce a fraction of a day.

pseudocode:

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

This produces the proper decimal value for how much of the day has elapsed and thus will display as a proper 'Time' in the cell with hh:mm formatting.