need a formula to fix time

  • Thread starter Thread starter Rpt_Me4NotBeingSmart
  • Start date Start date
R

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?
 
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
 
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
 
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)
 
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.
 
Back
Top