I'm eporting from a source some of the times come out just fine if is longer then one hour if less then one hour.
Example 1:26:54 AM is fine and I can format to 1:26:54
but if its :08:54 it exports with a leading ":" so I would need to get a formula to add "00" before the ":" then convert it to time format

Hi,

Am Wed, 25 Feb 2015 07:23:33 -0800 (PST) schrieb :

I'm eporting from a source some of the times come out just fine if is longer then one hour if less then one hour.
Example 1:26:54 AM is fine and I can format to 1:26:54
but if its :08:54 it exports with a leading ":" so I would need to get a formula to add "00" before the ":" then convert it to time format

try it with VBA:

Sub Time()
Dim LRow As Long, i As Long
Dim varTimes As Variant

With ActiveSheet
'Modify the column to your column with the times
LRow = .Cells(Rows.Count, "A").End(xlUp).Row
varTimes = .Range("A1:A" & LRow)
For i = 1 To UBound(varTimes)
If Left(varTimes(i, 1), 1) = ":" Then
varTimes(i, 1) = 0 & varTimes(i, 1)
End If
Next

With .Range("A1:A" & LRow)
.Value = varTimes
.NumberFormat = "h:mm:ss"
.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth,
_
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
End With
End With
End Sub

If you want a formula try:
=IF(LEFT(A1,1)=":",0&A1,A1)
and then format as expected

Or to get the correct time immediatly:
=IF(LEFT(A1,1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)) ,TIMEVALUE(A1))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi,

Am Wed, 25 Feb 2015 16:54:12 +0100 schrieb Claus Busch:

=IF(LEFT(A1,1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)) ,TIMEVALUE(A1))

or:
=IF(LEFT(A1,1)=":",TIMEVALUE(0&A1),TIMEVALUE(A1))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Not good with VBA But the your last formula worked like a charm thank you as always Claus


On Wednesday, February 25, 2015 at 9:23:37 AM UTC-6, wrote:
I'm eporting from a source some of the times come out just fine if is longer then one hour if less then one hour.
Example 1:26:54 AM is fine and I can format to 1:26:54
but if its :08:54 it exports with a leading ":" so I would need to get a formula to add "00" before the ":" then convert it to time format