Add colon to time automatically, still recognize it as time
 

Good morning! I need to type 0125 into a cell and it automatically add the colon (01:25 or 1:25- Either works). I have found several solutions to this BUT I also need Excel to still recognize the cell as "Time" so I can figure elapsed time in another cell. Thank you!

Add colon to time automatically, still recognize it as time
 

kellye.smiley wrote:

Good morning! I need to type 0125 into a cell and it automatically add
the colon (01:25 or 1:25- Either works). I have found several solutions
to this BUT I also need Excel to still recognize the cell as "Time" so I
can figure elapsed time in another cell.

I believe this requires VBA. This is what I use:

Sub fixTimes(what As Range)
Dim cell As Range
For Each cell In what
'fixed by Claus Busch
If (InStr(cell.Value, ".") < 1) And (IsNumeric(cell.Formula)) Then
Select Case cell.Formula
Case 0 To 2359
cell.Value = (CLng(cell.Formula) \ 100) & ":" & _
(CLng(cell.Formula) Mod 100)
End Select
End If
Next
End Sub

(Note that this doesn't check for invalid times, like "999".)

I find it convenient to call it in Workbook_SheetChange, like so:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case cell.Column
Case FIRSTTIMECOL To LASTTIMECOL 'globals defined elsewhere
fixTimes cell
End Select
Next
End Sub

--
There's never enough time to do all the nothing you want.
-- Calvin