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
.
Best Answer
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.