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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
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.
Actually it's the other-way round, you need to select the format that the dates are in, rather than using your local format.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,712
Members
416,130
Latest member
galgozzi

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