# 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
dsacker

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.
Professor J

Use datevalue and time value functions

Like this in b2 put =text(datevalue(left(a2,8),"mm/dd/yyyy")
SOLUTION
Saurabh Singh Teotia

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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

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.

KR
H--DateFieldFormula.xlsx

Sorry date should be 06/14/15 14:55:50.

KR
Enclosed your workbook..where i applied the formula..

Saurabh...
H--DateFieldFormula.xlsx

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.