Unable to get Excel to recognise date in column

Question

I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what.

I have a spreadsheet, exported from our exchange server, that contains a column with dates on. They have come out in US format even though I'm in the UK.

The column in question looks like this

04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011

In Excel, I have highlighted the column and selected Format Cells.... In this dialog box, I have selected the Date, selected English (United States) as the locale and chosen the matching date format from the list. I hit OK and try to sort the data by this column.

In the sort dialogue I choose this column, select sort on Values but the order only gives me options for A to Z, not oldest to newest as I would expect.

This in turn sorts the date data by the first two digits.

I am aware I could re-format this data to ISO and then the A to Z sort would work but I shouldn't have too, I'm obviously missing something. What is it?

EDIT: I messed up the bounty but this should have gone to @r0berts answer, his first suggestion of Text to Columns with no delimiter and choosing 'MDY' as the type of data works. Additionally, if you have a time (i.e. 04/21/2015 18:34:22), you need to first get rid of the time data. However after that the method suggested by @r0berts works fine.

Answer 1

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 told it via "Cell Format" and I couldn't agree more. Unfortunately it is only through step 1 from above that I have been able to make Excel recognize these text strings as dates. Obviously if you do this a lot it is pain in the neck and you might put together a visual basic routine that would do this for you at a push of a button. It can be as simple as this VBA code in Excel:

Sub RemoveApostrophe()

For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then    
CurrentCell.Formula = CurrentCell.Value
End If
Next

End Sub

Alternative 1: Data | Text to Columns

Update on leading apostrophe after pasting: You can see in the formula bar that in the cell where date was not recognised there is a leading apostrophe. That means in the cell formatted as a number (or a date) there is a text string that program thinks - you want to preserve as a text string. You could say - the leading apostrophe prevents the spreadsheet to recognise the number. You need to know to look in formula bar for this - because the spreadsheet simply displays what looks like a left-aligned number. To deal with this, select the column you want to correct, choose in menu Data | Text to Columns and click OK. Sometimes you will be able to specify the data type, but if you have previously set the format of the column to be your particular data type - you will not need it. The command is really meant to split a text column in two or more using a delimiter, but it works like a charm for this problem too. I have tested it in Libreoffice, but there is the same menu item in Excel too.

Alternative 2: Edit Replace in Libreoffice

This is the quickest and best way so far, but that does not work in MsOffice as far as I know. Libreoffice Calc has option to search/replace using Regexps (aka. Regular Expressions) - what you do is find the cell and replace with itself and in the process Calc re-recognises the number as number and gets rid of the leading apostrophe. It works extremely quick. Select your column. Ctrl-H opens find-replace dialogue. Check 'Current selection' and 'Use regular expressions'. In the find box enter ^[0-9] - which means 'find any cell that has a digit 0 to 9 in its first position'. In the replace box enter & - which for libreoffice means 'for replacement use string you found in the search box'. Click Replace All - and your values are recognised for numbers that they are. The beauty is - it works on cells that contain only numbers with a leading apostrophe, nothing else - i.e. it will not touch cells that contain apostrophe-a space (or two)-then number, or cells that contain capital O instead of zero or any other anomalies you want to correct by hand.

Answer 2

Select all the column and go to Locate and Replace and just replace "/" with /.

Answer 3

I was dealing with a similar issue with thousands of rows extracted out of a SAP database and, inexplicably, two different date formats in the same date column (most being our standard "YYYY-MM-DD" but about 10% being "MM-DD-YYY"). Manually editing 650 rows once every month was not an option.

None (zero... 0... nil) of the options above worked. Yes, I tried them all. Copying to a text file or explicitly exporting to txt still, somehow, had no effect on the format (or lack therefo) of the 10% dates that simply sat in their corner refusing to behave.

The only way I was able to fix it was to insert a blank column to the right of the misbehaving date column and using the following, rather simplistic formula:

(assuming your misbehaving data is in Column D)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

I could then copy the results in my new, calculated column over top of the misbehaving dates by pasting "Values" only after which I could delete my calculated column.

Answer 4

I had the exact same issue with dates in excel. The format matched the requirements for a date in excel, yet without editing each cell it would not recognise the date, and when you are dealing with thousands of rows it is not practical to manually edit each cell. Solution is to run a find and replace on the row of dates where i replaced the hypen with a hyphen. Excel runs through the column replacing like with like but the resulting factor is that it now recongnises the dates! FIXED!

Answer 5

This may not be relevant to the original questioner, but it may help someone else who is unable to sort a column of dates.

I found that Excel would not recognise a column of dates which were all before 1900, insisting that they were a column of text (since 1/1/1900 has numeric equivalent 1, and negative numbers are apparently not allowed). So I did a general replace of all my dates (which were in the 1800s) to put them into the 1900s, e.g. 180 -> 190, 181 -> 191, etc. The sorting process then worked fine. Finally I did a replace the other way, e.g. 190 -> 180.

Hope this helps some other historian out there.

Answer 6

It seems that Excel does not recognize your dates as dates, it recognizes them text, hence you get the Sort options as A to Z. If you do it correctly, you should get something like this:

Hence, it is important to ensure that Excel recognizes the date. The most simple way to do that, is use the shortcut CTRL+SHIFT+3.

Here's what I did to your data. I simply copied it from your post above and pasted it in excel. Then I applied the shortcut above, and I got the required sort option. Check the image.

Answer 7

I would suspect the issue is Excel not being able to understand the format... Although you select the Date format, it remains as Text.

You can test this easily: When you try to update the dates format, select the column and right click on it and choose format cells (as you already do) but choose the option 2001-03-14 (near bottom of the list). Then review the format of your cells.

I will suspect that only some of the date formats update correctly, indicating that Excel still treating it like a string, not a date (note the different formats in the below screen shot)!

There are work-arounds for this, but, none of them will be automated simply because you're exporting each time. I would suggest using VBa, where you can simply run a macro which converts from US to UK date format, but, this would mean copying and pasting the VBa into your sheet each time.

Alternatively, you create an Excel that reads the newly created exported Excel sheets (from exchange) and then execute the VBa to update the date format for you. I will assume the Exported Exchange Excel file will always have the same file name/directory and provide a working example:

So, create new Excel sheet, called ImportedData.xlsm (excel enabled). This is the file where we will import the Exported Exchange Excel file into!

Add this VBa to the ImportedData.xlsm file

Sub DoTheCopyBit()

Dim dateCol As String
dateCol = "A"        'UPDATE ME TO THE COLUMN OF THE DATE COLUMN

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\Dave\Desktop\"                   'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx")       'UPDATE ME (this is the Exchange exported file location)

Do While fileName <> ""

'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)

Workbooks(fileName).Worksheets("Sheet1").Copy _

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")

     Dim splitty() As String
     splitty = Split(Range(dateCol & row).Value, "/")
     Range(dateCol & row).NumberFormat = "@"
     Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
     Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
     row = row + 1
Loop


End Sub

What I also did was update the date format to yyyy-mm-dd because this way, even if Excel gives you the sort filter A-Z instead of newest values, it still works!

I'm sure the above code has bugs in but I obviously have no idea what type of data you have but I tested it with the a single column of dates (as you have) and it works fine for me!

How do I add VBA in MS Office?

Answer 8

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

Simple solution here - create new column use =datevalue(cell) formula then copy the formula into your other rows- a few seconds to fix

Answer 9

I figured it out!

There is a space at the beginning and at the end of the date.

1. If you use find and replace and press the spacebar, it WILL NOT work.
2. You have to click right before the month number, press shift and the left arrow to select and copy. Sometimes you need to use the mouse to select the space.
3. Then paste this as the space in find and replace and all your dates will become dates.
4. If there is space and date Select Data>Go to Data>Text to columns>Delimited>Space as separator and then finish.
5. All spaces will be removed.

Answer 10

If Excel stubbornly refuses to recognize your column as date, replace it by another :

• Add a new column to the right of the old column
• Right-click the new column and select Format
• Set the format to date
• Highlight the entire old column and copy it
• Highlight the top cell of the new column and select Paste Special, and only paste values
• You can now remove the old column.

Answer 11

I just simply copied the number 1 (one) and multiplied it to the date column (data) using the PASTE SPECIAL function. It then changes to General formating i.e 42102 Then go apply Date on the formating and it now recognises it as a date.

Hope this helps

Answer 12

All the above solutions - including r0berts - were unsuccessful, but found this bizarre solution which turned out to be the fastest fix.

I was trying to sort "dates" on a downloaded spreadsheet of account transactions.

This had been downloaded via CHROME browser. No amount of manipulation of the "dates" would get them recognised as old-to-new sortable.

But, then I downloaded via INTERNET EXPLORER browser - amazing - I could sort instantly without touching a column.

I cannot explain why different browsers affect the formatting of data, except that it clearly did and was a very fast fix.

Answer 13

My solution in the UK - I had my dates with dots in them like this:

03.01.17

I solved this with the following:

1. Highlight the whole column.
2. Go to find and select/ replace.
3. I replaced all the full stops with middle dash eg 03.01.17 03-01-17.
4. Keep the column highlighted.
5. Format cells, number tab select date.
6. Use the Type 14-03-12 (essential for mine to have the middle dash)
7. Locale English (United Kingdom)

When sorting the column all dates for the year are sorted.

Answer 14

I tried the various suggestions, but found the easiest solution for me was as follows...

See Images 1 and 2 for the example... (note - some fields were hidden intentionally as they do not contribute to the example). I hope this helps...

1. Field C - a mixed format that drove me absolutely crazy. This is how the data came directly from the database and had no extra spaces or crazy characters. When displaying it as a text for example, the 01/01/2016 displayed as a '42504' type value, intermixed with the 04/15/2006 which showed as is.

2. Field F - where I obtained the length of field C (the LEN formula would be a length of 5 of 10 depending on the date format). The field is General format for simplicity.

3. Field G - obtaining the month component of the mixed date - based on the length result in field F (5 or 10), I either obtain the month from the date value in field C, or obtain the month characters in the string.

4. Field H - obtaining the day component of the mixed date - based on the length result in field F (5 or 10), I either obtain the day from the date value in field C, or obtain the day characters in the string.

I can do this for the year as well, then create a date from the three components that is consistent. Otherwise, I can use the individual day, month, and year values in my analysis.

Image 1: basic spreadsheet showing values and field names

Image 2: spreadsheet showing formulas matching explanation above

I hope this helps.

Answer 15

SHARING A LITTLE LONG BUT MUCH EASIER SOLUTION TO SUBJECT..... No need to run macros or geeky stuff....simple ms excel formulae and editing.

mixed dates excel snapshot:

• The date is in the mixed format.
• Therefore, to make a symmetrical date format, extra columns have been created converting that 'mixed format' date column to TEXT.
• From that text we have identified the positions of "/" and middle text has been extracted determining date, month, year using MID formula.
• Those which were originally dates, formula ended with ERROR / #VALUE result. - Finally, from the string we created - we have converted those to dates by DATE formula.
• #VALUE were picked as it is by IFERROR added to DATE formula & the column was formatted as required (here, dd/mmm/yy)

* REFER THE SNAPSHOT OF EXCEL SHEET ABOVE (= mixed dates excel snapshot) *

Answer 16

This happens all the time when exchanging date data between locales in Excel. If you have control over the VBA program that exports the data, I suggest exporting the number representing the date instead of the date itself. The number uniquely correlates with a single date, regardless of formatting and locale. For example, instead of the CSV file showing 24/09/2010 it will show 40445.

In the export loop when you hold each cell, if it's a date, convert to number using CLng(myDateValue). This is an example of my loop running through all rows of a table and exporting to CSV (note I also replace commas in strings with a tag that I strip when importing, but you may not need this):

arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        strLine = ""
        For j = LBound(arr, 2) To UBound(arr, 2) - 1
            varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
            strLine = strLine & varCurrentValue & ","
        Next j
        'Last column - not adding comma
        varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
        strLine = strLine & varCurrentValue

        strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
        strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
        Print #intFileHandle, strLine
    Next i

Answer 17

I was not having any luck with all the above suggestions - came up with a very simple solution that works like a charm. Paste the data into Google Sheets, highlight the date column, click on format, then number, and number once again to change it to number format. I then copy and paste the data into the Excel spreadsheet, click on the dates, then format cells to date. Very quick. Hope this helps.

Answer 18

This problem was driving me crazy, then I stumbled on an easy fix. At least it worked for my data. It is easy to do and to remember. But I do not know why this works but changing types as indicated above does not. 1. Select the column(s) with the dates 2. Search for a year in your dates, for example 2015. Select Replace All with the same year, 2015. 3. Repeat for each year. This changes the types to actual dates, year by year.
This is still cumbersome if you have many years in your data. Faster is to search for and replace 201 with 201 (to replace 2010 through 2019); replace 200 with 200 (to replace 2000 through 2009); and so forth.

Answer 19

Select dates and run this code over it..

On Error Resume Next
    For Each xcell In Selection            
        xcell.Value = CDate(xcell.Value)     
    Next xcell                              
End Sub

Answer 20

No answers here have suggested Excel Power Query. I had a DateTime value (e.g. 2021-02-25T00:35:54.497Z) that excel couldn't interpret correctly. I needed both date and time. In a new Excel workbook, I used the "Get Data > From CSV" option to load into Power Query. Then loaded the results to excel sheet and the date values are recognized correctly. Note that type datetime vs type datetimezone makes a difference. I had to datetimezone to get the correct result.

Power Query - automated script from "Excel > Get Data" GUI

let
    Source = Csv.Document(File.Contents("C:\myFolder\DataSet_RefreshHistory.csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"refreshType", type text}, {"startTime", type datetimezone}, {"endTime", type datetimezone}, {"status", type text}, {"ReportName", type text}, {"WorkspaceId", type text}, {"serviceExceptionJson", type text}})
in
    #"Changed Type"

GET DATA

BEFORE and AFTER

Answer 21

I use a mac.

Go to excel preferences> Edit> Date options> Automatically convert date system

Also,

Go to Tables & Filters> Group dates when filtering.

The problem probably started when you received a file with a different date system, and that screwed up your excel date function

Answer 22

I usually just create an extra column for sorting or totaling purposes so use year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

That will provide a yyyymmdd result that can be sorted. Using the len(a1) just allows an extra zero to be added for months 1-9.