need a formula to fix time

[Rpt_Me4NotBeingSmart]

Lets say I have time in cell A1 that displays as :59:02. How can I write an
If formula to say If A1 contains :xx:xx, then insert a zero at the
beginning, otherwise do nothing?

 

[Mike H]

Hi,

You can't. Formula cannot push values out they can only pull values in. If
you explain exactly what you trying to do then someone will help.

Mike

 

[Dave Curtis]

Hi,

Is the value text or a time?

If the value is indeed a time, can you not just apply a custom format of
hh:mm:ss?

Dave

 

[Chip Pearson]

A formula can't change a cell, so you can't modify A1 with a formula.
In another cell, though, you can use

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

to prefix a "0" to the text in A1.

With code, you can modify the value in A1:

With Range("A1")
If StrComp(Left(.Text, 1), ":", vbBinaryCompare) = 0 Then
.Value = "0" & .Text
End If
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Bernard Liengme]

Put this in B1
=IF(LEFT(A1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)),A1)
best wishes

 

[Rpt_Me4NotBeingSmart]

I'm not sure of the answer you need so I will describe the situation:

I pull the time from a program, when it is output any time that is less than
an hour shows up as :mm:ss. If it is an hour or greater it comes out as
h:mm:ss. I want to be able to work with the time that is less than an hour. I
am sure there is some workaround for it in the way of a formula, but don't
know it.

 

[Rpt_Me4NotBeingSmart]

Bernard,

You're awsome, this is exactly what I needed. Thank you kind sir.