Prev: Display or hide control characters in Excel 2007
Next: How do I round up numbers when dividing?
From: Joseph Ryan on 16 Dec 2009 10:43
I am having the most difficult time. I have time values in the format
hh:mm:ss:sss, where the last three values are milliseconds. The problem is
that they are seperated from the seconds by a colon, which leaves me unable
to use them to calculate the differences between two of these times.
I need to find a way to either calcultate the time difference between the
two, or a way to get rid of the last kolon and millisecond values. Can
anyone help?
--
Regards,
Joseph


"Bzltyr" wrote:

> I have a column of numbers that are formatted as text. I can change the
> format to number but the value does not change. What is the shortcut for
> rentering the value that is currently in the cell?
From: Dave Peterson on 16 Dec 2009 11:06
You could use a helper column of cells that changes the text values to real time
values. The formula would be something like:

=--SUBSTITUTE(A1,":",".",3)

The -- converts text to a real number (like multiplying by -1 twice).

The 3 indicates that you want the 3rd colon changed to a comma.

Then format the cell(s) with a custom format of: hh:mm:ss.000

You could actually embed this formula into any existing formula:

=a1-b1
would become:
=SUBSTITUTE(A1,":",".",3) - SUBSTITUTE(b1,":",".",3)
Format the cell with this formula nicely: hh:mm:ss.000
The subtraction will force excel to treat each as a number. The -- stuff won't
be necessary.

But I'd fix the data once so that I wouldn't have to worry about fixing all the
formulas (and new formulas).



Joseph Ryan wrote:
>
> I am having the most difficult time. I have time values in the format
> hh:mm:ss:sss, where the last three values are milliseconds. The problem is
> that they are seperated from the seconds by a colon, which leaves me unable
> to use them to calculate the differences between two of these times.
> I need to find a way to either calcultate the time difference between the
> two, or a way to get rid of the last kolon and millisecond values. Can
> anyone help?
> --
> Regards,
> Joseph
>
> "Bzltyr" wrote:
>
> > I have a column of numbers that are formatted as text. I can change the
> > format to number but the value does not change. What is the shortcut for
> > rentering the value that is currently in the cell?

--

Dave Peterson
From: "David Biddulph" groups [at] on 16 Dec 2009 11:51
=--(LEFT(A1,LEN(A1)-4)&"."&RIGHT(A1,3)) and format the result as
hh:mm:ss.000
--
David Biddulph

"Joseph Ryan" <JosephRyan(a)discussions.microsoft.com> wrote in message
news:E20A8043-54DD-49D6-9530-9D2C4A9A4967(a)microsoft.com...
>I am having the most difficult time. I have time values in the format
> hh:mm:ss:sss, where the last three values are milliseconds. The problem
> is
> that they are seperated from the seconds by a colon, which leaves me
> unable
> to use them to calculate the differences between two of these times.
> I need to find a way to either calcultate the time difference between the
> two, or a way to get rid of the last kolon and millisecond values. Can
> anyone help?
> --
> Regards,
> Joseph
>
>
> "Bzltyr" wrote:
>
>> I have a column of numbers that are formatted as text. I can change the
>> format to number but the value does not change. What is the shortcut for
>> rentering the value that is currently in the cell?



 | 
Pages: 1
Prev: Display or hide control characters in Excel 2007
Next: How do I round up numbers when dividing?