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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
 
Upvote 0
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
 
Upvote 0
Hi Andy & PGC.

Thanks for your help.

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

Ted.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,528
Messages
6,102,358
Members
447,794
Latest member
rajmungur

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
Back
Top