1

I have a column in Excel with the following entries in text format:

 7.2013
 7.2014
 8.2013
 8.2014
 9.2013
 9.2014
10.2013
10.2014
11.2013
11.2014
12.2013
12.2014
 1.2015

How can I convert this text to date and have them displayed in mm-yyyy format?

I tried changing it to Custom format Format Cells > Numbers > Custom but using it in the first cell (7.2013) gets changed to 01-1900.

0

3 Answers 3

5

Dates are stored as a number: days since January 0, 1900 (day 1 is 1/1/1900). Times are stored as a decimal fraction of a day. So 7.2013 is January 7, 1900 4:49:52 AM. Formatting that as MM-YYYY gives you 01-1900.

Select the full column and do a Search - Replace for . to -.

This converts your number into a text string that Excel recognizes as month-year input and stores it as an appropriate date. Now you can apply the Custom format mm-yyyy to the column.

1
  • 1
    Added some explanation. Feel free to roll back or play with it.
    – fixer1234
    Apr 29, 2015 at 10:48
0

If you don't want to change your data, use this function:

=DATEVALUE((IF(RIGHT(LEFT(A1,2))=".",LEFT(A1),LEFT(A1,2)))&"/"&RIGHT(A1,2))

Assuming that "A1" is your cell.

Afterwards, change the format...

0

This works on my machine (64 bit Excel 2010 on Win 7)

=DATEVALUE(SUBSTITUTE(A1 , "." , "/"))

It should work whether the column is stored as text or not.

Another option is this:

=DATE(RIGHT(A1,4) , LEFT(A1,2) , 1)

This works even without the leading spaces on the month, because the LEFT(A1,2) picks up 7. which is interpreted as 7.0 and so still works as a month.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .