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


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.

  • I would suspect the issue is Excel not knowing how to do this - you can test. 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). I will suspect that only some of the date formats update correctly, indicating that Excel still treating it like a string, not a date!
    – Dave
    Mar 17, 2015 at 9:14
  • What is the format of the exported data? Is it CSV or XLSX?
    – Excellll
    Mar 18, 2015 at 14:44
  • I've had a similar issue and the Excel DATEVALUE() function seems to have solved the problem
    – Manu G
    Mar 12, 2022 at 18:25

22 Answers 22


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

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.

  • I address both your points in my OP. Splitting the date values to columns (and then re-integrating in ISO format) is of course a possibility, but given that Excel has extensive built in date functions I'd like to be able to leverage them with the valid date data I have. Your second option is exactly as I describe what is not working for me.
    – Patrick
    Sep 26, 2014 at 15:05
  • Dear Patrick, please read carefully. Or you can use Libre Office Calc - with your data this works straight away - you simply pre-format column for Date - English (USA) and upon pasting do "Paste Special" "Unformatted text" - just tell the dialog box that language is English(USA) and don't forget to check the box to recognize special numbers. In my answer I tried to hint as hard as I could that Excel is inconsistent with this (buggy) and you have to resort to these steps 1 and then 2 to achieve the result you want.
    – r0berts
    Sep 26, 2014 at 16:28
  • 1
    I should indeed have read your question properly. Your first suggest does indeed work. You should have had the bounty. Thanks for your assistance.
    – Patrick
    Apr 8, 2015 at 16:20
  • 1
    Really good answer!!!
    – Adders
    May 17, 2018 at 14:35
  • Tragically, a macro which recorded the text to columns procedure does not correct the sort issue! Can anyone help with that? Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 4), TrailingMinusNumbers:=True
    – Piecevcake
    May 1, 2020 at 1:34

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

  • 2
    Can you explain what this does to fix the problem?
    – fixer1234
    Aug 28, 2015 at 20:26
  • This really worked and was easy. The replacement thing just remove the leading zeros Jul 7, 2017 at 18:08
  • 6
    First, THIS WORKS. BEST solution. FAR better than the checked solution :) . To fixer1234 and @PedroGabriel: It's not that it removes zeros. In the act of replacing cell contents, Excel evaluates whether the resultant cell contains a formula, text, or a number. After doing the "/" replacement, it interprets the cell to now contain a number (which a date technically is to Excel), so it is now correctly sortable. (Clever users may recognize the technique of doing find/replace with an "=" character in a similar scenario with Excel reinterpreting contents.) Anyway Jair has the ideal solution :) May 2, 2019 at 14:53
  • @MicrosoftShouldBeKickedInNuts today I have no idea why I said that about the zeroes, it really had nothing to do with zeroes... This is the best answer, still using this one. Cheers May 2, 2019 at 18:09
  • Sort worked after this was manually done, and by far the easiest! BUT recorded macro didn't fix the sort! Any Ideas? Selection.Replace What:="/", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False I tried adding the replace format d/m/yy h.mm AM/PM, that didn't work. I suspect that if we find the missing parameter we can dispense with the character replace!
    – Piecevcake
    May 1, 2020 at 1:25

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)


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.


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!


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.


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.


  • 2
    Unfortunately that shortcut does not work for me.
    – Patrick
    Sep 26, 2014 at 15:07
  • What does the shortcut do in your system Patrick.
    – Firee
    Sep 29, 2014 at 6:21
  • 3
    The shortcut doesn't seem to do anything at all. I've got several systems at my disposal running Office 2007, 2010 and 2013 and none of them react to the CTRL SHIFT 3 shortcut. CTRL 1 correctly pops up the 'format cell' dialogue.
    – Patrick
    Sep 29, 2014 at 9:28
  • 1
    The Ctrl+Shift+3 keyboard shortcut, referenced somewhat sloppily as “Ctrl+Shift+#” here, means “Applies the Date format with the day, month, and year.” in Excel 2007, 2010, 2013, and 2016. (Specifically, on my Excel 2013, it is “d-mmm-yy”.) The Microsoft document doesn’t explain that the cells must already contain … (Cont’d) Jun 3, 2017 at 22:03
  • 1
    (Cont’d) … values that Excel recognizes as date/time data, and it seems like nobody here has clearly explained that, since Patrick is in England, his version of Excel is recognizing strings like 04/08/2012, 04/09/2009, and 04/01/2010 as 4-Aug-2012, 4-Sep-2009, and 4-Jan-2010, respectively, and is treating 04/21/2011 as a text string (because there is no 21st month). Since some of the values are text (and some of the values, presumably, have actually been misinterpreted), formatting (such as by the Ctrl+Shift+3 keyboard shortcut) isn’t going to do any good. Jun 3, 2017 at 22:11

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)!

enter image description here

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

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 <> ""

Workbooks.Open (directory & fileName)

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


fileName = Dir()


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

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?

  • well... I'm going to leave this for a couple of days to give it a bit of extra exposure but this looks like a solid answer. This is absolutely crazy though, I can't beleive this is needed. As it is I've fixed the data at source with a small function in powershell to flip it round to ISO format at which point I don't care if Excel recognises it as a date :/
    – Patrick
    Mar 17, 2015 at 15:50


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


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.
  • The reason the find and replace won't work with the spacebar is that the gap is most likely a tab.
    – Patrick
    Nov 17, 2015 at 11:54

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.
  • I've tried this, it behaves the same with the new column. See Dave's answer, the screen cap at the top is what I get not matter how many times I move it between columns.
    – Patrick
    Mar 17, 2015 at 15:42
  • Try to force the issue by using the DATEVALUE(old column) function in the formula on the new column, then use the Number formatting dropdown list on the Home ribbon bar to choose Short/Long Date.
    – harrymc
    Mar 18, 2015 at 8:24

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


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.


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


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.


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.

  • (1) While it is nice to post a screen image to demonstrate that your answer actually works, we prefer that the formulas in your solution be posted as text in the body of your answer, so people can copy and paste them. (2) You seem to understand the problem — that numeric dates like 04/08 are ambiguous. 04/08 will be interpreted as April 8 in some locales (such as the United States) and 4-Aug in others (such as England).  But these are ambiguous precisely because the month number and the day-of-the-month are different. … (Cont’d) Jun 4, 2017 at 20:22
  • (Cont’d) …  So why would you use un ambiguous dates like 01/01 in your answer?  (3) Actually, I’m not sure you do understand the problem, or that your answer actually does work, inasmuch as your sheet has rows where the month # is 15; that appears to be an obvious error.  And, given that 04/15 is unambiguous — it must mean April 15 — it doesn’t make sense that your method parses 01/11 as the 1st day of the 11th month. … (Cont’d) Jun 4, 2017 at 20:22
  • (Cont’d) …  (4) It should be pointed out that, at best, you answer will handle only dates that look like nn/nn/nnnn.  It will fail on dates like 5/8, 5/08, 5/28, 05/8, and 11/8.  Also — fun fact — your answer will fail on dates ≤ May 17, 1927 and ≥ October 17, 2173.  Never mind the Y10K problem; we may start having issues as soon as 154 years from now!  (5) You say, “I can do this for the year as well, then create a date from the three components that is consistent.”  So why don’t you, and show it? … (Cont’d) Jun 4, 2017 at 20:23
  • (Cont’d) …  It’s especially hard to verify that your method works when you present an incomplete version of it; one that doesn’t show the month, day and year being reassembled into a date.  (6) As I told Patsaeed, it would have been nice if you had used the OP’s data, from the question, rather than making up your own.  Failing to use the OP’s data, it might have been nice if you had used Patsaeed’s, so at least we’d be able to compare apples to apples. … (Cont’d) Jun 4, 2017 at 20:23
  • (Cont’d) …  And, whether you use the OP’s data or somebody else’s, it would have been nice if you had shown the original input to your scenario.  (For example, for Row 7, is it 01/11/2016 or 11/01/2016?)  And, if you’re going to use your own data, it would have been nice if you had included dates from the same month that demonstrate (for example) how 1/11 and 1/21 are handled differently. And why have duplicate data?  You’ve wasted 19 rows by using the same four values multiple times, spread out over 23 rows. Jun 4, 2017 at 20:23

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:

enter image description here

  • 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) *

  • (1) While it is nice to post a screen image to demonstrate that your answer actually works, we prefer that the formulas in your solution be posted as text in the body of your answer, so people can copy and paste them. (2) Your formulas as shown in your image are wrong, inasmuch as they refer to Columns V, W, and X when they should be referring to Columns B, C, and D. … (Cont’d) Jun 4, 2017 at 20:20
  • (Cont’d) … (3) It would have been nice if you had used the OP’s data, from the question, rather than making up your own.  And, whether you use the OP’s data or your own, it would have been nice if you had shown the original input to your scenario (e.g., for Row 9, it is 5/8/2014 or 5/08/2014).  And, if you’re going to use your own data, it would have been nice if you had included dates from the same month, to demonstrate (for example) how 5/8 and 5/28 are handled differently. And why have duplicate data?  You’ve wasted six rows by using the same values more than once. Jun 4, 2017 at 20:20

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

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.


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.


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
  • I tried this, it added "12:00:00 AM" in every empty cell in the selection (a whole column). And sort didn't recognise the dates.
    – Piecevcake
    May 1, 2020 at 1:12

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

    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}})
    #"Changed Type"


enter image description here


enter image description here


I use a mac.

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


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


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.

  • 2
    This won't help since the dates aren't being recognised as dates. Also, using extra columns for sorting purposes is rarely necessary
    – CallumDA
    Sep 26, 2014 at 11:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .