leading zeros in dates

Dake

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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.

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
I should have included the 'else' portion as follows: =IF(LEFT(AN3,1)="0",RIGHT(AN3,(LEN(AN3)-1)),AN3)
 

Dake

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Dake

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,676
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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

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