Unable to get Excel to recognise date in column
Unable to get Excel to recognise date in column
Unable to get Excel to recognise date in column
Re: Unable to get Excel to recognise date in column
**The problem:** Excel does not want to recognize dates as dates, even though through "Format cells - Number - Custom" you are explicitly trying to tell it these are dates by "`mm/dd/yyyy`". As you know; when excel has recognized something as a date, it further stores this as a number - such as "`41004`" but displays as date according to format you specify. To add to confusion excel may convert only part of your dates such as 08/04/2009, but leave other e.g. 07/28/2009 unconverted.
**Solution: steps 1 and then 2**
**1)** Select the date column. Under Data choose button Text to Columns. On first screen leave radio button on "**delimited**" and click ***Next***. Unclick any of the delimiter boxes (any boxes **blank**; no checkmarks) and click ***Next***. Under column data format choose **Date** and select **MDY** in the adjacent combo box and click ***Finish***. Now you got date values (i.e. Excel has recognised your values as `Date` data type), but the formatting is likely still the locale date, not the `mm/dd/yyyy` you want.
**2)** To get the desired US date format displayed properly you first need to select the column (if unselected) then under **Cell Format** - *Number* choose **Date** AND select Locale : **English (US)**. This will give you format like "`m/d/yy`". Then you can select Custom and there you can either type "`mm/dd/yyyy`" or choose this from the list of custom strings.
**Alternative 0 : use LibreOffice Calc**. Upon pasting data from Patrick's post choose Paste Special (`Ctrl+Shift+V`) and choose Unformatted Text. This will open "Import Text" dialog box. Character set remains Unicode but for language choose English(USA); you should also check the box "Detect special numbers". Your dates immediately appear in the default US format and are date-sortable. If you wish the special US format MM/DD/YYYY you need to specify this once through "format Cells" - either before or after pasting.
One might say - Excel should have recognised dates as soon as I
*(Réponse tronquée)*
**Solution: steps 1 and then 2**
**1)** Select the date column. Under Data choose button Text to Columns. On first screen leave radio button on "**delimited**" and click ***Next***. Unclick any of the delimiter boxes (any boxes **blank**; no checkmarks) and click ***Next***. Under column data format choose **Date** and select **MDY** in the adjacent combo box and click ***Finish***. Now you got date values (i.e. Excel has recognised your values as `Date` data type), but the formatting is likely still the locale date, not the `mm/dd/yyyy` you want.
**2)** To get the desired US date format displayed properly you first need to select the column (if unselected) then under **Cell Format** - *Number* choose **Date** AND select Locale : **English (US)**. This will give you format like "`m/d/yy`". Then you can select Custom and there you can either type "`mm/dd/yyyy`" or choose this from the list of custom strings.
**Alternative 0 : use LibreOffice Calc**. Upon pasting data from Patrick's post choose Paste Special (`Ctrl+Shift+V`) and choose Unformatted Text. This will open "Import Text" dialog box. Character set remains Unicode but for language choose English(USA); you should also check the box "Detect special numbers". Your dates immediately appear in the default US format and are date-sortable. If you wish the special US format MM/DD/YYYY you need to specify this once through "format Cells" - either before or after pasting.
One might say - Excel should have recognised dates as soon as I
*(Réponse tronquée)*