convert time without using a semi colon

**daznav** · 07-22-2010, 09:24 PM

Guys

having trouble with inputting a time...when i put in 230303 in cell A1 i want it to read 23:03:03 but unfortunatly i get a date and time when i format the cell to hh:mm:ss

any ideas

cheers

**rethguals** · 07-22-2010, 09:38 PM

Put in number in column Z(or any...outside of data range)
A1 : =TIMEVALUE(TEXT(Z1,"00"":""00"":""00"))
Hide column Z

**daznav** · 07-22-2010, 09:43 PM

Sorry but what number in colum z i need cell A1 blank to start with so i can input 230303 what does the number in Z signify

**rethguals** · 07-22-2010, 10:06 PM

Put in Sheet1 module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As String

With Target
If .Count > 1 Then Exit Sub
If .Column = 1 Then
X = .Value
Application.EnableEvents = False
If Len(X) < 6 Then
X = "0" & X
End If
.Value = TimeSerial(Left(X, 2), Mid(X, 3, 2), Right(X, 2))
End If
Application.EnableEvents = True
End With
End Sub

**NBVC** · 07-22-2010, 10:08 PM

rethguals,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**daznav** · 07-22-2010, 10:39 PM

Thank you it does work but also found a custom format thanks again

**DonkeyOte** · 07-23-2010, 02:18 AM

For the benefit of others - the Custom Format would be: 00\:00\:00

However the above is merely a mask, the underlying value remains unaffected and is thus not converted to Time.

If you conduct further calcs on this data you must remember to account for this, eg if you had multiple entries in A1:A10 and wished to SUM the cumulative time you could use:

=SUMPRODUCT(--TEXT(A1:A10,"00\:00\:00"))
format as [hh]:mm:ss

(ie result would be a time value)