Excel question: Dates

Factual Questions
1

I have a .txt file that I want to upload into Excel. The date format in the text file is mm/yy. So I open the file and choose fixed-width. I position the divider lines where they’re supposed to be. The dates say, for example, 02/08 or 10/06. So far, so good.

When I click Finish the dates are messed up. The year becomes the day, the month is MMM (which is fine), and the year is 08. So instead of getting OCT 06 I’m getting 6 OCT (with 2008 seen in the formula bar; i.e., 10/6/2008).

I tried specifying MDY and MYD in the upload. (There are no options that only have month and year. This is the same thing you see once the file is in Excel and you click on Text-to-columns.) It insists on 6 Oct, 8 Feb, 5 Jun, or whatever. I’ve also tried uploading the date as Text. This gives me the right date – e.g., 02/08 – only it’s not formattable to Feb 08 since Excel doesn’t think it’s a date.

So: how do I get 02/08, 10/07, or 06/05 to import from a text file to Excel such that they are dates that display as Feb 02, Oct 07, or Jun 05 and not have the year become the day and have a bogus 2008 year?

2

In Excel, Choose Format, Cells, then Custom. You should be able type the format you want under that.

3

Yep. You want MMM DD.

Edit: On second thought, that will only generate “Oct 06” or whatever. To get “OCT 06” you’ll have to use the formula =UPPER(), which will complicate things a bit.

Is the all caps that important?

4

Sorry, read right over the actual question. Looks like losing all caps isn’t a problem, but having the year become a day is.

[del]I know of no way to let Excel format this as a date and not have it provide a year. Excel counts all time with serial numbers, so there are no abstract mm/dd date in Excel. You can format the cells as plain text and then use formulas to manipulate the data in them as if they were dates, but if you tell Excel to format them as dates, you’re gonna get years, AFAIK.[/del]

Edit: Dammit, I keep confusing myself: “MMM DD” in the above post should be “MMM YY.” You can force Excel to use that format, but it’s going to assume the first day of the month, so 10/06 will become 10/1/2006.

5

Bingo.

I knew there had to be some way of getting mmm-yy from mm/yy!

Thanks.

6

Well, yes, however, Excel thinks for the user and decides in it’s infinate Microsoft wistom that the data in the cell are equilivant to MM/YY and formats that data as MM/this year (10/02 would be 10/08, and the data in the cell would be 10/02/08)

format your column as text or if you want a date, format the column as text, and in the next column run this formula:

=CONCATENATE(LEFT(celladdy,2),"/01/",RIGHT(celladdy,2))

which will make 10/02 10/01/02

drag that puppy down the length of the new column, copy it, then paste special, paste as value. THEN format the colum as MM/YY in custom.

edited because I have DD YY issues/confusion

7

The user types “MMM/YY” in the custom format window as I described above, and it works fine without all of the steps you describe.

8

Or perhaps not. Still experimenting, but I think I’m wrong, and laramary is correct.