Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formula returns night time by mistake | Excel Discussion (Misc queries) | |||
Time formula (difference of predicted and actual time) | Excel Discussion (Misc queries) | |||
Calculating Time with Date/Time checking in formula | Excel Worksheet Functions | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
template or formula for start time -finish time -total hours ple | New Users to Excel |