43

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.

  • 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 '15 at 9:14
  • What is the format of the exported data? Is it CSV or XLSX? – Excellll Mar 18 '15 at 14:44

30 Answers 30

69

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 : 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.

Data | Text to Columns

Update on leading apostrophe after pasting: You can see in formula bar that in the cell where date is not recognised there is a leading apostrophe. That means in the cell formatted as a number (or date) there is 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 problem 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.

  • 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 '14 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 '14 at 16:28
  • 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 '15 at 16:20
  • Suggestion 1 works like a charm. thanks. – Adam Dec 1 '17 at 15:28
  • Really good answer!!! – Adders May 17 '18 at 14:35
6

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

  • Can you explain what this does to fix the problem? – fixer1234 Aug 28 '15 at 20:26
  • This really worked and was easy. The replacement thing just remove the leading zeros – PedroGabriel Jul 7 '17 at 18:08
  • 1
    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 :) – MicrosoftShouldBeKickedInNuts May 2 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 – PedroGabriel May 2 at 18:09
3

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!

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.

3

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.

2

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:

http://i.stack.imgur.com/Qb4Pj.png

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.

http://i.stack.imgur.com/yAa6a.png

  • 2
    Unfortunately that shortcut does not work for me. – Patrick Sep 26 '14 at 15:07
  • What does the shortcut do in your system Patrick. – Firee Sep 29 '14 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 '14 at 9:28
  • 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) – Scott Jun 3 '17 at 22:03
  • (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. – Scott Jun 3 '17 at 22:11
2

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

  • 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 '15 at 15:50
2

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

1

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 '15 at 11:54
0

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 '15 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 '15 at 8:24
0

Had similar problem, tried formatting the data as date but to no avail. Then I realized there was a leading space in the date, edited the field and removed the space - hey presto everything ok. Not great for large amounts of data, but thinking about it further I could have maybe checked it and edited the data in Wordpad or a similar editor.

0

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

0

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.

0

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.

0

So I had the same issue. None of the solutions above either worked for me or I found them too complex for my skill level.

What I ended up doing was doing a text-to-columns on the date delimiter. I then used the date() function to recreate the date using the day, month and year cells. Worked.

  • The accepted answer really didn’t work for you?   You seem to have posted something that is, essentially, a more complicated and bothersome (labor-intensive) version of r0berts’s answer. – Scott Jun 3 '17 at 22:22
0

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) – Scott Jun 4 '17 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) – Scott Jun 4 '17 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) – Scott Jun 4 '17 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) – Scott Jun 4 '17 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. – Scott Jun 4 '17 at 20:23
0

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) – Scott Jun 4 '17 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. – Scott Jun 4 '17 at 20:20
0

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

0

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
0

I found that CAST AS smalldatetime solves my problem. Solution found here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f0f8dd05-4212-428f-8099-748e87c637ae/format-data-from-sql-server-view-as-date-in-excel?forum=sqlkjpowerpivotforexcel

That of course only works, if you have access to the underlying query.

0

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.

0

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.

0

I was able to fix the problem after realizing that the "Show Formulas" was selected. in Excel 2016, go to Formulas, then the Formula Auditing section and make sure "Show Formulas" isn't selected. Fixed my problem.

0

I had the similar problem, but I noticed I had an extra spaces at the end of the date. I removed the spaces with replace command and now date sorting works fine.

0

this problem made me almost break my laptop

long story short, i had a similar problem where the dates are working just fine in some cells but keep flipping in others regardless if i copy paste or enter manually, i did the whole data text to column and cell formatting solutions and all of that didn't work.

the solution actually is not in excel, its in the region and language setting.. i know right !!!

to have the dates display as MM/DD/YYYY in the formats tab change the format to US

to have the dates display as DD/MM/YYYY in the formats tab change the format to UK

Voila !

0

Suppose that the stubborn dates that look like YYYY-MM-DD are in column A

Go to a new column lets say column C and in the formula bar and use =DATEVALUE(A2) and copy it down

copy paste as values Column C to Column A Format Column A as short date.

cheers

  • 1
    Welcome to Super User. This duplicates an answer from June 2016 by Mike. With 30 answers, it was probably easy to miss. :-) – fixer1234 May 21 at 8:27
-1

I still had problem after following several answers. Than in the format, chose 14-Mar-01 and changed locale to English (United States).. And it worked perfectly. Hope this helps.

-1

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

-1

I found a very easy answer. Dates were formatted 10.11.2018 and were not being recognised as dates. having first formatted the column as dates, I did a Ctrl-H search and replace of "." with "/". All dates were instantly recognised.

-2

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 '14 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.