Solved Convert text string to Date and Time format in Excel

November 2, 2015 at 15:10:34
Specs: Windows 7
I imported data from another program into Excel and it is giving me the date and time in this format:

10012015 01:40

I need it to look like this:
10/01/2015 01:40

The data is in Cell R72. When I use the formula below, it comes up as 8/15/03 1:40 instead of 10/01/2015 01:40.

=DATE(MID(R72,1,4),MID(R72,5,2),MID(R72,7,2))+TIME(MID(R72,10,2),RIGHT(R72,2),0)
Any help would be appreciated!

message edited by rajasperson


See More: Convert text string to Date and Time format in Excel

Report •


#1
November 2, 2015 at 16:52:15
✔ Best Answer
Try this:

=DATE(MID(R72,5,4),LEFT(R72,2),MID(R72,3,2))+TIMEVALUE(RIGHT(R72,5))

The syntax for the DATE() Function is:

=DATE(Year , Month , Day)

The Year starts at the 5th Character and is 4 Characters long
For the Month we get the First Two characters from the LEFT() side of the text string.
For the Day we start at the 3rd Character and get 2 characters.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
November 3, 2015 at 07:50:43
It worked!!! Thank you!!

Report •
Related Solutions


Ask Question