Unable to get Excel to recognise date in column

46

10

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.

Patrick

Posted 2014-09-26T10:46:13.467

Reputation: 1 132

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 – 2015-03-17T09:14:14.277

What is the format of the exported data? Is it CSV or XLSX? – Excellll – 2015-03-18T14:44:24.607

Answers

72

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.

r0berts

Posted 2014-09-26T10:46:13.467

Reputation: 1 585

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 – 2014-09-26T15:05:48.400

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 – 2014-09-26T16:28:50.300

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 – 2015-04-08T16:20:45.280

Suggestion 1 works like a charm. thanks. – Adam – 2017-12-01T15:28:25.720

Really good answer!!! – Adders – 2018-05-17T14:35:32.233

8

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

Jair Bressani

Posted 2014-09-26T10:46:13.467

Reputation: 79

Can you explain what this does to fix the problem? – fixer1234 – 2015-08-28T20:26:54.887

This really worked and was easy. The replacement thing just remove the leading zeros – PedroGabriel – 2017-07-07T18:08:55.603

2First, 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 – 2019-05-02T14:53:25.620

@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 – 2019-05-02T18:09:32.170

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!

Warren Rocchi

Posted 2014-09-26T10:46:13.467

Reputation: 31

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.

Lucster

Posted 2014-09-26T10:46:13.467

Reputation: 31

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.

Laurie Allen

Posted 2014-09-26T10:46:13.467

Reputation: 31

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

Mike

Posted 2014-09-26T10:46:13.467

Reputation: 21

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

Chainsaw

Posted 2014-09-26T10:46:13.467

Reputation: 128

2Unfortunately that shortcut does not work for me. – Patrick – 2014-09-26T15:07:30.027

What does the shortcut do in your system Patrick. – Firee – 2014-09-29T06:21:34.187

3The 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 – 2014-09-29T09:28:14.950

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 – 2017-06-03T22:03:07.313

(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 – 2017-06-03T22:11:17.117

Thank you Scott, you are right that no-one had explained that. Now you have it seems obvious! – Patrick – 2017-06-26T12:33:36.607

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?

Dave

Posted 2014-09-26T10:46:13.467

Reputation: 24 199

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 – 2015-03-17T15:50:20.300

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.

JailDoctor

Posted 2014-09-26T10:46:13.467

Reputation: 11

The reason the find and replace won't work with the spacebar is that the gap is most likely a tab. – Patrick – 2015-11-17T11:54:18.840

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

Mpho Sehlako

Posted 2014-09-26T10:46:13.467

Reputation: 1

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.

Ros

Posted 2014-09-26T10:46:13.467

Reputation: 1

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

Patsaeed

Posted 2014-09-26T10:46:13.467

Reputation: 11

(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 – 2017-06-04T20:20:53.340

(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 – 2017-06-04T20:20:57.147

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.

Charlie

Posted 2014-09-26T10:46:13.467

Reputation: 1

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.

Eran

Posted 2014-09-26T10:46:13.467

Reputation: 1

(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 – 2017-06-04T20:22:55.410

(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 – 2017-06-04T20:22:58.693

(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 – 2017-06-04T20:23:01.280

(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 – 2017-06-04T20:23:54.040

(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 – 2017-06-04T20:23:56.870

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

Kathryn

Posted 2014-09-26T10:46:13.467

Reputation: 1

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

Mor Sagmon

Posted 2014-09-26T10:46:13.467

Reputation: 101

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.

Mike

Posted 2014-09-26T10:46:13.467

Reputation: 1

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.

Wally

Posted 2014-09-26T10:46:13.467

Reputation: 1

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.

harrymc

Posted 2014-09-26T10:46:13.467

Reputation: 306 093

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 – 2015-03-17T15:42:23.430

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 – 2015-03-18T08:24:37.113

-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

Stephenie

Posted 2014-09-26T10:46:13.467

Reputation: 1

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

Paul

Posted 2014-09-26T10:46:13.467

Reputation: 1

2This won't help since the dates aren't being recognised as dates. Also, using extra columns for sorting purposes is rarely necessary – CallumDA – 2014-09-26T11:59:22.507