need a formula to fix time
 

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?

need a formula to fix time
 

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

"Rpt_Me4NotBeingSmart" wrote:

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?

need a formula to fix time
 

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

"Rpt_Me4NotBeingSmart" wrote:

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?

need a formula to fix time
 

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)




On Wed, 18 Feb 2009 05:37:03 -0800, Rpt_Me4NotBeingSmart
wrote:

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?

need a formula to fix time
 

Put this in B1
=IF(LEFT(A1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)),A 1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rpt_Me4NotBeingSmart" wrote
in message ...
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?

need a formula to fix time
 

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.

"Dave Curtis" wrote:

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

"Rpt_Me4NotBeingSmart" wrote:

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?

need a formula to fix time
 

Bernard,

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



"Bernard Liengme" wrote:

Put this in B1
=IF(LEFT(A1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)),A 1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rpt_Me4NotBeingSmart" wrote
in message ...
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?