EXCEL Convert number to date time

I have a report in Excel that has the date stored as a GENERAL number  2015061119420000.  I need to convert this number to mm/dd/yyyy hh:mm:ss.  I have tried converting several ways but nothing is working.

KR
kvrogersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
If you put that value in A1, then in another cell, this formula should work:

=MID(TEXT(A1, "0"), 5, 2) & "/" & MID(TEXT(A1, "0"), 7, 2) & "/" & LEFT(TEXT(A1, "0"), 4)

Then, wrap it with the DATEVALUE function:

=DATEVALUE(MID(TEXT(A1, "0"), 5, 2) & "/" & MID(TEXT(A1, "0"), 7, 2) & "/" & LEFT(TEXT(A1, "0"), 4))

And of course, set the Custom Format to mm/dd/yyyy.
ProfessorJimJamMicrosoft Excel ExpertCommented:
Use datevalue and time value functions

Like this in b2 put =text(datevalue(left(a2,8),"mm/dd/yyyy")
Saurabh Singh TeotiaCommented:
Assuming you have value in A1..then in b1 you can use this formula..

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

Also post that remember to format the cell in the format you want to which is mm/dd/yyyy hh:mm:ss

Saurabh...
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

[ fanpages ]IT Services ConsultantCommented:
Two further suggestions...

If 2015061119420000 is presently yyyyddmmhhmmssnn, then...

=TEXT(DATE(LEFT(A1,4),MID(A1,7,2),MID(A1,5,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2)),"mm/dd/yyyy hh:mm:ss")
11/06/2015 19:42:00

If 2015061119420000 is presently yyyymmddhhmmssnn, then...

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2)),"mm/dd/yyyy hh:mm:ss")
06/11/2015 19:42:00
kvrogersAuthor Commented:
I am attaching a copy of the column with the date in it as it appears in the report.  I cannot include the full report as it contains patient information.

FANPAGES :  I thought your second formula would work as the date should appear as 06/14/2015  14:55:50

I highlighted A2 and entered the formula but that did not work.    Am I supposed to put this formula somewhere else like in the Custom number format.  
Sorry for my ignorance.

Thanks for your help
KR
H--DateFieldFormula.xlsx
kvrogersAuthor Commented:
Sorry date should be 06/14/15 14:55:50.  

KR
Saurabh Singh TeotiaCommented:
Enclosed your workbook..where i applied the formula..

Saurabh...
H--DateFieldFormula.xlsx
[ fanpages ]IT Services ConsultantCommented:
FANPAGES :  I thought your second formula would work as the date should appear as 06/14/2015  14:55:50

I highlighted A2 and entered the formula but that did not work.    Am I supposed to put this formula somewhere else like in the Custom number format.  
Sorry for my ignorance.

No need to apologise.

Using my second formula as a base (with the references to A1 changed to A2, to match your data), please place this formula in cell [B2]:

=TEXT(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(MID(A2,9,2),MID(A2,11,2),MID(A2,13,2)),"mm/dd/yyyy hh:mm:ss")

Then copy cell [B2] to the MS-Windows clipboard & paste down column [ B ] to the extent of your data (cell [B111]).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
Actually BOTH solutions worked.  It was just me.  I forgot to also adjust the Custom Number formatting.  Once I did that.  They actually BOTH worked.  Thanks to both of you for all your help.
[ fanpages ]IT Services ConsultantCommented:
:)

That's great.  Thanks for closing the question so promptly!
Saurabh Singh TeotiaCommented:
You are welcome..Always happy to help.. :-)

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.