Can anyone tell me how to convert numbers into dates in Excel?

Question

Hi,

I have an excel spreadsheet with tens of thousands of entries in it and many (though not all) of the date fields have been manually entered in this format - 11/03 (stands for November 2003)- under the Purchase date field.

I'd like all purchase dates to appear in the following format (I'm using a random example date) - 17 March 2003.

Given that many of my dates are accurate to the day, I'd prefer to keep the date and for those entries where it's just MM/YY I'd like to make each entry the first of that month. ie. 11/03 would become 1 November 2003.

Please could you tell me how to get these five character entries converted into date strings?

Thanks in advance!

Answer 1

I am guessing that if you are able to see the dates in this format that the column is set to text format. Leave this as is for the time being.

Create 3 new columns to the right of your current date column.

Select the whole sheet by clicking on the button in the top right corner of the column and row identifiers. Sort your table using the date column. As the entries are text you will be prompted to choose how to treat numbers. Take the first option : Sort anything that looks like a number as a number. The entries that have MM/YY should right at the bottom of your spreadsheet.

Select the cells that you wish to convert and choose Text to columns... from the data menu in Excel.

Choose the Delimited radio button and click next. In the Other box put a / and tick the other box.

Click Next, and choose text as the format for each column. (click on the column header and choose the text radio button for each)

Then click Finish.

You should now have two columns one with DD and one with YY.

In the third column that you created enter the following formula in the first cell :

=CONCATENATE("01/",A1,"/",B1)

Where A and B are the columns containing the split out date numbers.

Now double click on the black dot in the bottom right of the cell you just entered the formula into and it should copy all the way down to the bottom of the list.

Select the newly created cells with the full date and copy to clipboard.

Then select the first cell that contains a MM/YY date and choose paste special from the edit menu.

Choose to paste only values and click OK.

Now delete the three columns you created earlier.

Create another new column to the right of your date column.

In the first cell enter the following formula :

=DATEVALUE(A1)

Where A is the column with the dates.

Double click on the black dot in the bottom right of the cell you just entered the formula into and it should copy all the way down to the bottom of the list.

Now select the new column that should have five figure numbers in it and choose Format > Cells and choose a date format.

Now delete your original date column and you should be good.

Answer 2

Open your excel document. Now, highlight the column or row where you would like to make these changes. Right click anywhere in the highlighted area and select FORMAT CELLS from the drop down menu. The first tab you see on a newly open window will be Number, select that if not already selected (it usually defaluts to that tab). Highlight the word DATE. To the right will now appear different formats for you to chose from. Select your format preference and then OK.

Now, most people like the date format not to have those pesky dash's. But you will note that it is not a format option. Simple fix: Instead of selecting DATE in the left hand area, select CUSTOM instead. Now, back to the right you will see the word Type: directly under this you will see m/d/yyyy. Highlight m/d/yyyy and enter dd Mmm yy. This will appear as 1 Jan 07. You just need to enter the format of your choice. To see the complete day month year, enter dd Mm yyyy, It will look like 1 January 2007.

Good luck!

Answer 3

1>Select the range of cells and right click on it
2>Select Format Cell option or(ctrl + 1)
3>Select Number tab
4>Select Custom in the Category
5>In the Type Box type
[$-409]dd - mmmm - yyyy
6>Press OK

7>If Your old data dose not change
8>You have to change it manually by
9>Select range of Cells and press f2 and press enter simultaneously until all Your data is changed

Answer 4

Select the cell range where your dates are.
On the Format menu, select Cells.
On the first tab, you see a list of possible formats. Click on the one you want and then click OK.