leading zeros in dates

[Dake]

I imported a column of dates, some of which have a leading zero, like 09/21/2011. Since other dates don't have a leading zero (11/11/2011), the column doesn't sort chronologically. The various options in Text To Columns didn't work for me. Then I tried my old standby- copy and paste to Notepad, then back into a new (unformated) Excel column. Notepad rocks for getting rid of all formatting, allowing Excel to interpret the raw text correctly

 

[6StringJazzer]

Do you have a question?

 

[Candyman8019]

Give this a try: Assuming the date text is in cell AN3...
=IF(LEFT(AN3,1)="0",RIGHT(AN3,(LEN(AN3)-1)))

 

[Candyman8019]

I should have included the 'else' portion as follows: =IF(LEFT(AN3,1)="0",RIGHT(AN3,(LEN(AN3)-1)),AN3)

 

[Dake]

Do you have a question?

Actually I didn't, but now I do. My solution didn't work! Even though the leading 0s are gone, it still doesn't sort. I can't get these dates to sort correctly

 

[Dake]

Something weird's happening... here the formula I tried: =IF(LEFT(A2,1)="0",RIGHT(A2,(LEN(A2)-1)),A2) -just replacing your AN3 with my A2. When A2 = 09/06/2020, the result is 43714. I tried copy/paste the data as text into a new column...same result.

 

[Fluff]

Format the cell as a date.
43714 is 6th Sep 2020

 

[Dake]

Something weird's happening... here the formula I tried: =IF(LEFT(A2,1)="0",RIGHT(A2,(LEN(A2)-1)),A2) -just replacing your AN3 with my A2. When A2 = 09/06/2020, the result is 43714. I tried copy/paste the data as text into a new column...same result.

 

[Dake]

I finally noticed some improperly entered dates that were causing trouble, and things are sorting now. I don't know why the above formula didn't work- it looks accurate. Thanks everyone

 

[jasonb75]

Without a bit more detail best guess would be that your original data was in UK date format but your regional settings are for US format (or vice versa). Your initial attempts with text to columns and notepad may have changed any dates where the day is no later than the 12th to a valid format but also flipped the month and day while the rest remain in the initial text format.

Try your original data (before you made any attempts to change it) with text to columns, making sure that you select the correct date format for your regional settings from the dropdown on the last screen, not the format of the dates in the document.