kvrogers

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

KR

Use datevalue and time value functions

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

Like this in b2 put =text(datevalue(left(a2,8)

SOLUTION

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 yyyydd

=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 yyyymm

=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

If 2015061119420000 is presently yyyydd

**mm**hhmmssnn, then...=TEXT(DATE(LEFT(A1,4),MID(

11/06/2015 19:42:00

If 2015061119420000 is presently yyyymm

**dd**hhmmssnn, then...

=TEXT(DATE(LEFT(A1,4),MID(

06/11/2015 19:42:00

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

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

ASKER

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

KR

KR

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

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.

:)

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

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

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

Saurabh...

Saurabh...

=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.