# Converting a Number to Date and Time

1. ## Converting a Number to Date and Time

Good morning,

I have one that's tricking me up a bit. I have some data that is presented in a date code of 201201012354 or (Jan. 1, 2012 at 23:54) and I'm trying to convert the string of numbers to a 01/01/2012 23:54 format but my =text formula has not been working. Anyone have any suggestions that might work.

201201010354 I want to equal 01/01/2012 03:54
201201010454 " " 01/01/2012 04:54

I've tried using =Text(B1,"0000-00-00-00:00")+0 but that has returned the "Value" error.  Register To Reply

2. ## Re: Converting a Number to Date and Time

Try this version

=TEXT(B1,"0000-00-00 00\:00")+0  Register To Reply

3. ## Re: Converting a Number to Date and Time

Try this assuming the original number is in the format
yyyymmddhhmm

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TEXT(RIGHT(A1,4),"00\:00")  Register To Reply

4. ## Re: Converting a Number to Date and Time Originally Posted by Jonmo1 Try this assuming the original number is in the format
yyyymmddhhmm

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TEXT(RIGHT(A1,4),"00\:00")
Thanks Jonmo.

I had tried =DATE(YEAR(LEFT(B1,4)),MONTH(MID(B1,5,2)),DAY(MID(B1,7,2))) but when I added the "TIME" function failed because of too many functions.

The one you gave me worked great.

Much appreciated.  Register To Reply

5. ## Re: Converting a Number to Date and Time

You should try Daddy's solution too. It's pretty slick.  Register To Reply

6. ## Re: Converting a Number to Date and Time Originally Posted by Jonmo1 You should try Daddy's solution too. It's pretty slick.
I initially tried it and got a large number in return and thought my data cells showing up as 2.012E+11 was the issue. Then I realized, like an amateur, that the large number it returned was the date in excel format...once I reformatted the cell...worked perfect too.

Thanks DLL.  Register To Reply