# leading zeros in dates

#### Dake

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### 6StringJazzer

##### Well-known Member
Do you have a question?

#### Candyman8019

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

#### Candyman8019

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

#### Dake

##### New Member

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

##### New Member
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

##### MrExcel MVP, Moderator

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

#### Dake

##### New Member
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

##### New Member
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

##### Well-known Member
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.

Replies
6
Views
110
Replies
1
Views
104
Replies
8
Views
478
Replies
6
Views
110
Replies
1
Views
341

1,130,173
Messages
5,640,588
Members
417,152
Latest member
DayTimeSeby

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

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