Desperate for help with date formatting

tarmand

Board Regular
Joined
Jul 11, 2002
Messages
196
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

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

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

tarmand

Board Regular
Joined
Jul 11, 2002
Messages
196
That merges two rows in that column together and gives me a #VALUE!

error.
 

tarmand

Board Regular
Joined
Jul 11, 2002
Messages
196
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Board Regular
Joined
Jul 11, 2002
Messages
196
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Maybe you hve some spaces before or after your data?

Try

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

instead.
 

Forum statistics

Threads
1,181,786
Messages
5,932,041
Members
436,816
Latest member
Composh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top