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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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

ADVERTISEMENT

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

error.
 

tarmand

Board Regular
Joined
Jul 11, 2002
Messages
196

ADVERTISEMENT

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,148,526
Messages
5,747,216
Members
424,069
Latest member
kamkwok1hh

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