0

Id appreciate if someone can help. I have a column with the following values:

DATETIME
1401032014
1401032014
1401010629
1401032011
1401010629
1401032011

The cell is a number (or can be displayed as text). It shows the datetime. Example:

1401032011:
year: 14
month:01
day:03
hours: 20
minutes: 11

How can I split the value and concatenate into datetime? Notice i have only two last digits of the year - 14, so the full datetime should have the format like this "2014-01-03 20:11"

Thanks a lot in advance.

Gyuzal
  • 1,581
  • 10
  • 52
  • 99
  • 1
    [this](http://superuser.com/questions/335132/how-do-i-parse-a-nonstandard-date-format-in-excel) and [this](http://stackoverflow.com/questions/13056051/convert-date-from-yyyymmdd-to-mm-dd-yyyy-excel/13056198#13056198) answers may help – Dmitry Pavliv Mar 03 '14 at 15:00

1 Answers1

0

If you only want the formula to return text use:

="20"&LEFT(A1;2)&"-"&MID(A1;3;2)&"-"&MID(A1;5;2) & " " & MID(A1;7;2) &":"&MID(A1;9;2)

If you want the formula to return a datetime value use:

=DATE(2000+LEFT(A1;2);MID(A1;3;2);MID(A1;5;2))+(MID(A1;7;2)*60+MID(A1;9;2))/1440

And then (for the last one) format the datetime value at will.

I always prefer, when possible, to use date instead of datevalue because datevalue interprets date_text argument based on regional settings and will return an error with different Regional Settings.

CRondao
  • 1,883
  • 2
  • 12
  • 10