Converting Cell Format

[tpolglaze]

Hello.

I want to convert a cell format from

00:00:00:00 to hh:mm:ss.00

(note the "." rather than ":" before last 2 digits)

Any suggestions?

Thanks.

Ted.

 

[Andrew Poulsom]

Can you give some examples of before and after?

 

[tpolglaze]

Before: 00:01:54:83
After: 00:01:54.83

Note that the first one is a GENERAL format. The second one is a CUSTOM time format which includes 1/100ths of a second.

 

[Andrew Poulsom]

Try:

=TEXT(SUBSTITUTE(A1,":",".",3),"hh:mm:ss.00")+0

formatted as hh:mm:ss.00.

 

[tpolglaze]

Excellent - works perfectly.

Thank you.

 

[pgc01]

Hi Ted, Andrew

I believe excel will coerce the text to date automatically:

=SUBSTITUTE(A1,":",".",3)+0

formatted as hh:mm:ss.00.

HTH
PGC

 

[Andrew Poulsom]

Hi Ted, Andrew

I believe excel will coerce the text to date automatically:

=SUBSTITUTE(A1,":",".",3)+0

formatted as hh:mm:ss.00.

HTH
PGC

Indeed it will.

 

[tpolglaze]

Hi Andy & PGC.

Thanks for your help.

Just out of interest, what does the "+0" at the end do?

Ted.

 

[pgc01]

Hi Ted

SUBSTITUTE is a text function and so the result is text. You don't want the result 00:01:54.83 to be text, just a bunch of characters, what you want it to be is excel time.

To convert text to excel time you can do it explicitly, with the timevalue function:

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

or use a property of excel that is type coercion. Excel "tries hard" to calculate the result of a formula. If the types of the operators are not the adequate for the operation excel tries to convert them to the appropriate types.

That's what we are doing. When we add 0, excel sees the operator + and knows that it expects 2 numbers. Since the first is a text excel converts it to number. It then adds 0 which doesn't change the value.

So adding 0 to a text is a convenient way of converting it to number.

Hope it's clear
PGC