Convert decimal text into hh:mm:ss

**k_doe** · 03-15-2010, 03:09 PM

Hi,

I hope you can help me.I have a lot of raw data were the date and time column is spread over two columnsand the time appears in a very funny decimal format. See below:

Column A Column B
08/09/2007 600.4
08/09/2007 1232.23
14/09/2007 1914
11/09/2007 2519.09

I have given four different examples above and I need to convert these fields into a single time/date stamp (dd/hh/yyyy hh:mm).

So the above example should come in one field as
08/09/2007 06:04
08/09/2007 12:32
14/09/2007 19:14
12/09/2007 01:19

My raw data consists of more than 15.000 records and I am unable to convert all of these numbers manually. Can you help me?

Also, please note the last example. My times often exceed the normal 24hour spread but includes up to 30 hours at time which needs to be added to the next day.

Hope this makes sense.

I appreciate all the help you can offer!

Thank you.
Kris

**NBVC** · 03-15-2010, 03:15 PM

Try:

=A1+TIME(LEFT(TEXT(B1,"0000"),2),MID(TEXT(B1,"0000"),3,2),0)

copied down, where A1 and B1 contain first combination

Format as custom: m/d/yy hh:mm

**shg** · 03-15-2010, 03:23 PM

Or =A1 + DOLLARDE(B1/100, 60) / 24

I think your first example is wrong.

**NBVC** · 03-15-2010, 03:27 PM

.. i always think in simple terms..

**k_doe** · 03-22-2010, 06:27 PM

Amazing!!!!! Took me days NOT to figure it out and then thank goodness I stumbled across this site.

Works perfectly, thank you Shg for all your help!!!! Thanks also for NBVC.

Hugs