Desperate for help with date formatting

[tarmand]

I am downloading information into excel. One fo the columns is called period/year. The data dumps from the other program in the 0012004 format meaning January, 2004. When I sort it, the information from 2003 isn't showing up first since it is sorting numerically. Is there a way to format that column to make the date show as 04-Jan, 03-May, etc so that it will sort properly?

 

[fairwinds]

Hi,

you could use a formula to turn them into real dates:

=DATE(RIGHT(A1,4),LEFT(A1,3),1)

 

[tarmand]

Stupid question time. Where do I put that formula?

 

[fairwinds]

In an adjascent column, that you then use for your sort.

 

[tarmand]

That merges two rows in that column together and gives me a #VALUE!

error.

 

[fairwinds]

I mean like this:

Book1
	A	B	C	D
1	0012004	2004-01-01
2	0022005	2005-02-01
3	0112004	2004-11-01
4
Sheet3

The result of the formulas can be formatted to show the dates as you wish.

 

[tarmand]

Still does not work for me. Is it because my column is formatted as text? It still merges the two rows of that column into one.

 

[fairwinds]

The column where you have your date codes are formatted as text, yes. The column where you put the formula should be formatted as date.

If you select the cell and the paste the formula in the formula bar, instead of in the cell, it will not merge. (or type it in). Then you just select the cell and drag the formula down the column.

 

[tarmand]

I'm sorry to be a pain, but I've done that and still get an error.

I have text formatted column B2 showing 0122003 and I am putting your formual into date formatted C2. Still get Value error.

 

[fairwinds]

Maybe you hve some spaces before or after your data?

Try

=DATE(RIGHT(TRIM(A1),4),LEFT(TRIM(A1),3),1)

instead.