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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

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

ADVERTISEMENT

Excellent - works perfectly.

Thank you.
 

pgc01

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

ADVERTISEMENT

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,884
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,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

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