Changing number to date time format

**Saw-whet Owl** · 12-05-2012, 10:48 AM

Hello,

I recently downloaded some data that includes a column of date time information. The date time appears as one number (ex: 201206010053 for 2012-06-01 00:53). I though that I could change the number into a date time format by using the custom option under the format cells number option. However, I entered yyyy-mm-dd hh:mm into the custom format option and got ############# as the output for the date times. Any suggestions of what I must do to change 201206010053 to 2012-06-01 00:53?

Thank you!

Screen shot 2012-12-05 at 9.46.47 AM.png

**benishiryo** · 12-05-2012, 10:57 AM

hi Saw-whet Owl, welcome to the forum. use this formula to convert them into a proper format first:
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIMEVALUE(MID(A1,9,2)&":"&RIGHT(A1,2))
you can then change them to your custom formatting

**VKS** · 12-05-2012, 11:00 AM

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

this will give you 6/1/2012
then change the date format to 3/14/01 13:30

**Saw-whet Owl** · 12-05-2012, 11:01 AM

Fantastic! Thank you Benishyrio and VKS!

**daddylonglegs** · 12-05-2012, 11:03 AM

You could use this formula

=TEXT(A1,"00-00-00 00\:00")+0

then custom format as required

**Canvasshoes** · 12-10-2012, 08:12 PM

Originally Posted by VKS

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

this will give you 6/1/2012
then change the date format to 3/14/01 13:30

My data is not returning the correct dates when I use this formula. Would someone please help me manipulate the formula so that I can get the correct dates? The data is in MDY and is either 5 or 6 digits for instance 80601 for August 6, 2001. Or 100300 for October 3, 2000 (the data is recovered data from an ancient computer system and didn't always make the transition in usable format).

Thanks!