Remove leading zeros from dates in mm/dd/yyyy format (so they can sort chronologically)

sodajunkie

New Member
Joined
Mar 24, 2009
Messages
4
Hi,

I export a report from SAP to Excel which generates leading zeros in the format mm/dd/yyyy, which Excel doesn't seem to recognize as dates. I changed the Format from whatever the query generated to Date format, so that the filter lists the dates chronologically. But the Sort function still doesn't.

Ex: 01/01/2007 shows up before 10/01/2006 when sorted.

How can I fix this? I can go into each cell and hit the Enter key, which causes 01/01/2007 to turn into 1/1/2007. But I can't do that for 2000 lines :(

Help?! :confused: :eek:
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hello sodajunkie, welcome to MrExcel

Try this.....

Select your column of dates and use

Data > Text to columns > Finish

This should convert the whole column to real dates which will then sort chronologically.......
 
Upvote 0

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi & Welcome to the Board!

Try selecting the dates column and going Data>TextToColumns>Next>Next and on the third screen select a date import format of DMY or MDY depending on your locale and then click Finish.
 
Upvote 0

sodajunkie

New Member
Joined
Mar 24, 2009
Messages
4
One thing I just tried was the DATEVALUE formula, which converts 01/01/2007 into 37622.

Then I changed the Format for that field to mm/dd/yyyy and it displays as 1/1/2007.

Then I can Copy and Paste Values to change the cell contents from a formula to a date.

Seems like a reasonable workaround, but I'd love to know if there's an easier way to get rid of the zeros :)
 
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Try this: select the dates > Data > Text to Columns, press Next twice, on the third screen tick Date, Select the appropriate format and click Finish,
 
Upvote 0

sodajunkie

New Member
Joined
Mar 24, 2009
Messages
4
Thanks everyone. Wow, you guys are fast! Why have I been spending countless hours on the phone with my IT department, and why didn't I find this site sooner?

Very gratefully yours,
sodajunkie
 
Upvote 0

Forum statistics

Threads
1,190,637
Messages
5,982,068
Members
439,753
Latest member
mnyankee

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