3

My question is very similar to this problem, but for Excel: how do I convert mmyy to last day of month in netezza or this one: Create a date from Credit Card expire in MMYY format.

Essentially I have a column filled with dates that have been written as mmyy i.e.

0215
0215
0215
0315
0315

The column has been saved as the data type: "Special".

How do I convert this data into a useable format? I don't mind if we put it into dd/mm/yyyy and use the first or the last day of the month that's fine. Is there an Excel function that I could use or is this something I would have to do in VBA and if so, how?

Community
  • 1
  • 1
James Willcox
  • 631
  • 1
  • 10
  • 15
  • I understand that the cell is displaying *0215* but if you look in the formula bar, are you seeing *0215* or *215*? Do you want to change the values in the cells or create another column with date values? Do you want the first or last day of the month; pick one. –  Apr 01 '15 at 00:55
  • seeing 215, new column and first – James Willcox Apr 01 '15 at 00:57

3 Answers3

4

With the 'dates' starting in A2, use this formula in an unused column to the right,

=DATE(20&RIGHT(A2, 2), LEFT(TEXT(A2, "0000"), 2), 1)

Fill down as necessary. You will have to format as a date or you will receive the raw date value like 42064.

If you need the end-of-month, add 1 to the month and change the day to 0 like,

=DATE(20&RIGHT(A2, 2), LEFT(TEXT(A2, "0000"), 2)+1, 0)
4

30 characters: 

=1*REPLACE("1/"&A1,5,1,"/201")

Subject to the dates concerned the 20 may be redundant.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Bonus points for brevity but a dozen or so extra characters will guard against *215* vs *0215* e.g. `=1*REPLACE("1/"&TEXT(A1, "0000;@"),5,0,"/20")`. My m/d/yy regional system also interprets the OP's first sample value as 02-Jan-2015. A small change to `=1*REPLACE(TEXT(A1, "0000;@"),3,0,"/1/20")` addresses the regional issue. –  Apr 01 '15 at 03:51
2

use this

=DATEVALUE("01/"&LEFT(A1;2)&"/20"&RIGHT(A1;2))

enter image description here

do not forget to change Format Cells to "Date" for column "B"

Vasily
  • 5,707
  • 3
  • 19
  • 34