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. 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". However after that the method suggested by works fine. 18:34:22), you need to first get rid of the time data. What is it?ĮDIT: I messed up the bounty but this should have gone to answer, his first suggestion of Text to Columns with no delimiter and choosing 'MDY' as the type of data works.
Macro displaying date as number iso#
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. This in turn sorts the date data by the first two digits. 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. I hit OK and try to sort the data by this column. 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. In Excel, I have highlighted the column and selected Format Cells. They have come out in US format even though I'm in the UK. I have a spreadsheet, exported from our exchange server, that contains a column with dates on. I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what.