Converting Cell Format

tpolglaze

New Member
Joined
May 30, 2006
Messages
15
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

tpolglaze

New Member
Joined
May 30, 2006
Messages
15
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.
 

tpolglaze

New Member
Joined
May 30, 2006
Messages
15
Excellent - works perfectly.

Thank you.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
May 30, 2006
Messages
15
Hi Andy & PGC.

Thanks for your help.

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

Ted.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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
 

Forum statistics

Threads
1,171,463
Messages
5,875,656
Members
433,145
Latest member
nzltrippa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top