Excel: Date formatting

Factual Questions
1

I have a file in which the date column is three or four digits (MMYY). e.g., 411 or 1210. I want to make all dates the first of the month (since I don’t know the day) and format them as MM/01/YY. But I can’t seem to find a combination of moves that will let me do this.

For now, the solution is to copy the dates into Notebook, manually put a slash between the month and the year, change the slashes to /01/, and copy the results back into Excel. (The other solution is to sort and do several F&Rs. This is a pain because changing all of the elevens to /01/11 might result in ‘11/01/11/01/11’, for example.)

2

If the date in is cell A1, then:

=LEFT(a1,LEN(a1)-2)&"/01/"&RIGHT(a1,2)

gets you a text string that looks like what you want.

3

Assuming your dates are in column A, add a new column B and copy this formula down:

=LEFT(A1,(LEN(A1)-2))&"/01/"&RIGHT(A1,2)

ETA: Great minds think alike. :slight_smile:

4

Use the following formula, replacing “A1” with whatever cell contains the four digit date:
=DATE(A1-100*TRUNC(A1/100),TRUNC(A1/100),1)

5

Maybe it’s just how my PC is set up, but when I do that, I get dates in the early Twentieth Century.

6

Standing on the shoulders of giants!

Add the extra formula to get an actual, sortable Excel date

=value(LEFT(A1,(LEN(A1)-2))&"/01/"&RIGHT(A1,2))

This formula assumes that it’s YY, format. If you enter a Dec 2009 data as 129 it all goes pear shaped.

7

Assuming the date is in cell D4, this works for 3 or 4 digit dates:
=IF(LEN(D4)=4,CONCATENATE(“01/”,MID(D4,2,2),"/",RIGHT(D4,2)),CONCATENATE(“01/0”,MID(D4,1,1),"/",RIGHT(D4,2)))

8

This formula works for me. Probably something about different versions of Excel. Assuming the dates are all post-2000, this formula should work:
=DATE(2000+A1-100*TRUNC(A1/100),TRUNC(A1/100),1)

9

FWLIW, the variation to create dates using the dd/mm/yy format as per the civilised world is:
=VALUE(“01/”&LEFT(A3,LEN(A3)-2)&"/"&RIGHT(A3,2))

Same assumptions on YY format apply.

10

Thanks, everyone. I’ll try it next month.