Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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?

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 99
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default 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?

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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?





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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?

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time formula returns night time by mistake ferde Excel Discussion (Misc queries) 7 October 11th 08 03:51 PM
Time formula (difference of predicted and actual time) deb Excel Discussion (Misc queries) 7 September 26th 08 04:55 PM
Calculating Time with Date/Time checking in formula cmatera Excel Worksheet Functions 2 August 11th 08 01:38 PM
Formula to find Stop Time from Start Time and Total Minutes Jonathan Bickett Excel Worksheet Functions 5 March 7th 07 06:22 PM
template or formula for start time -finish time -total hours ple cc New Users to Excel 1 March 27th 06 06:06 PM


All times are GMT +1. The time now is 05:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"