How to handle time format hh:mm:ss:xxx

**Simplicissimus1668** · 12-06-2012, 11:52 AM

Hello everyone,

I hope this question hasn't already been asked.

I have a datalogger that has a time format output of the form hh:mm:ss:xxx and would like Excel to realize that it is dealing with a time. Now I know that usually in Excel the normal time format would be hh:mm:ss.xxx (or a "," instead of "." if using the German version). Is there any way to define a custom time format such that Excel realizes that what I have is a time format (and how would I have to define it to work) or, alternatively, if there is a simple way to exchange the last ":" for "." (or ",")?

Help is appreciated. Thanks.

**Sindhus** · 12-06-2012, 11:55 AM

To convert last : to ".", you can use substitue formula =SUBSTITUTE(E3,":",".",3) Replace E3 with your cell reference

**ChemistB** · 12-06-2012, 11:58 AM

To get it to recognize it as time.
1. Format column B (or any empty column) to Custom hh:mm:ss.0;@
2. Building on Sindhus' formula
=TIMEVALUE(SUBSTITUTE(A2,":",".",3))
Does that work for you?

You can then copy>Paste Special>values back over top of your original data (will need to custom format that column also)