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

[sodajunkie]

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?!

 

[barry houdini]

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.......

 

[Richard Schollar]

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.

 

[sodajunkie]

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

 

[VoG]

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,

 

[sodajunkie]

Hey Richard,

Thanks for the Text-to-Columns tip! This is exactly the simple solution I needed

 

[sodajunkie]

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