asked on

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.

Start Free Trial

[ fanpages ]

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

kvrogers

ASKER

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

kvrogers

ASKER

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

KR

Saurabh Singh Teotia

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

Saurabh...
H--DateFieldFormula.xlsx

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kvrogers

ASKER

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 ]

:)

That's great. Thanks for closing the question so promptly!

Saurabh Singh Teotia

You are welcome..Always happy to help.. :-)

Saurabh...