Excel! Need help with DATES!?

Question

I have a column that has just a raw #....like this ^041706

This needs to be 04/17/06

I have tried EVERYTHING!!!

Help!

Using FORMAT ..>DATE returns the wrong date....

Try it....

If I convert USING DATE FORMAT I get this date....03/18/14 ...... the format is good...

but its NOT 04/17/06 the date it SHOULD say....get it?

Answer 1

hi Tim

People is just giving you solutions as if this is just a matter of formating your information, when it is not the issue...Considering the information you gave us... ^041706

1)Select your column
2)In data menu select "text to column".
3)select "delimited" in original data type and click next
4)In "delimiters type" enter ^ in "other: "and click next
5)In "data preview" your information should separated into 2 columns. Column one is going to be selected by default (column one is for the ^).
6) Select "do not importcolumn (skip)" on column data format
7)click on the second column in data preview (where you should see just dates) and then select "date" on column data format. Defaul format is MDY
8)Click finish

This method will transform your information as you want.

please vote!

Answer 2

If your raw # is in A1, put this formula in an adjacent cell to convert it to a date:

=DATE(RIGHT(A1,2), MID(A1,2,2), MID(A1,4,2))

This assumes the format of your raw# is always the same -- i.e. that the date starts with the 2nd character and is 6 chars. long.

If that's not always the case, the formula will need to be refined.

Two notes:
- you may have to format the result to a date format
- Excel may assume that the "06" means 1906. The easiest way to change this is by adjusting the formula:
=DATE(RIGHT(A1,2)+100, MID(A1,2,2), MID(A1,4,2))

If you need more info, add mored details, or e-mail.
Cheers.
.

Answer 3

Select the column so that the whole column is highlighted.

Click format on toolbar,
select cells,
select date,
select locale, us/uk etc
select date style you require.

Enter dates as 4 oct if this year, or 4 oct 72 if earlier if UK
Turn it around if US eg oct 4 72

Answer 4

You haven't quite tried everything. Highlight the column where you'll be posting dates. Then from your dropdown menus, click on Format, then format cells. In the Number categor, choose "Date". Then over on the right you'll see a little window called "Type". And you'll see several date style options to choose from. Your preference will be there.

Answer 5

Highlight the cells you wish to change.
Right click with your mouse.
Select "Format cells"
Click on the Number tab
Select "Date" option (should be the 5th or so choice down from the top on the list).
You will see many ways to display dates. Select the one you need.

:)