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" 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?  |