I have numbers in a column in the Format 10 11, etc - is there anyway i can format the column to change these numbers into dates or even put a / between the 10/11 to show that its the 10th of November. thanks

Hi, Assuming that there is always a single space between day and month use this formula - data (e.g., 10 11) in cell A1 and formula in cell B1

=DATE("2010",RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1))You will need to change the hardcoded year as required.

Format cell B1 with a date format (Right-click, select Format Cells... then Numbers Tab and select an option under Date or use a custom format - if you just want day and month try "dd/mm" for 10/11 or "mmmm dd" for November 10)

Drag the formula down alongside your dates. Stop when year changes and change the year in the formula and drag the revised formula down again.

After formatting as you want, you could Copy - Paste Special - Values and Number Formats.

Then you get rid of the formulas and can erase your original column of dates.Doing the above gives you 'real' Excel dates that can be manipulated further such as extracting data for a range of dates, or calculating number of days between dates.

Here are my test results:

A B 1 10 11 10-Nov-2010 2 1 2 01-Feb-2010 3 20 1 20-Jan-2010Regards

Hi Humar, This worked great for what I needed - the cobol program I wrote extracted over 5000 dates in the Format 10 11, etc so it was great to get them into a more readable format in Excel. Thanks for your help.

Ask Your Question

Weekly Poll