MS Excel dates not sorting correctly et al

lr1277

Hi. I am having a problem where date based data is not being sorted from earliest to latest. There is data from about 4 days earlier this month that is showing at the bottom of the spreadsheet, instead of data from the most recent dates.

I am adding to a apreadsheet of share data and the share price on certain days. I am upto 1140 rows approximately.

The short problems are:

1) The shares are no longer in date order based on sorting the column with the date data. I have tried setting cell format to 14/03/01* and 14/03/01 to no avail. Sometimes 2 adjacent dates seem to be quite different widths even though the numbers seem to be similar widths. I have tried left alignment but that has made all the dates the same width and has not helped with my sorting problem.

2) I have another problem in that I sort the data on different ways. All my columns have a title. However when I used to do a custom sort, it would show me the column titles. Now it says Column B, Column C etc. This happens whether I choose the whole spreadsheet, or select all the data rows and not includde the row with the column headings.

In terms of the problems,

Some background:

I am constructing a spreasheet with share data and how it changes over time.

So the initial columns are the share name, ticker and the price on the day I entered it into the spreadsheet.

At the end of the week, I add the closing price on Friday and get the spreadsheet to calculate the % change since the day I added it to the spreadsheet.

To do this calculation, I copy all the date based data to another worksheet and then sort it into ticker and then date order. This makes it easier to enter Friday's closing price by filling down when I have more than one entry for a share. When finished, I copy this data back to the original worksheet and sort it back into date order, which is where my problem shows.

It is possible this copying from 1 worksheet to another is causing problems, but I have been doing this for the last few months and htis is the first time this problem has happened.

Any thoughts on what I might be doing wrong or how I could fix these problems?

Many thanks in advance.

Le_Kirk

When you click on Data, Sort and you select your column of dates do you tell Excel to treat numbers as numbers or text?

victor2

Are your dates entered as proper dates and not in plain text? There should be no need to copy the data to another sheet to sort it, and then copy it back. You can sort in situ.

When you sort, you should include the heading row in you selected data for sorting then make sure the "My data has headers" box is ticked in the sort options.

If you're worried about messing up your existing worksheet (which you do of course have a backup of), don't save it, or save it under another name while you play with it.

Carrot007

Set the cell format to numeric temporarily to ensure they realy are dates (anything after the decimal is the time).

Anything not looking like a number then is not a date and needs to be fixed.

Heedtheadvice

...and if they are entered as textual dates rather than the correct numetic data that are correctly interpreted as date data/time then they can be converted.

Post back if that is the problem on how to do the conversion. Test as outlined above by Carrot.

WaywardDriver

This may not be your problem but dates were a headache for me when copying from csv files. When copied and pasted into the master worksheet, some came across incorrectly as mm/dd/yyyy and others correctly as dd/mm/yyyy. e.g. 10/12/2020 came across as 12/10/2020 but 31/12/2020 was OK as 12/31/2020 isn't a valid date. 

Heedtheadvice

Was that the fault of the source csv file formating or incorrect interpretation by Excel?

WaywardDriver

Heedtheadvice said:

Was that the fault of the source csv file formating or incorrect interpretation by Excel?

The problem was inconsistent across different financial organisations - in all cases the csv dates displayed correctly and the MONTH function gave the expected result. However when copying there was no problem with some dates, others always came across as mm/dd/yyyy, others as either dd/mm/yyyy or mm/dd/yyyy depending on whether the mm part was greater than 12. In the end I implemented a different solution for each organisation. 

lr1277

Le_Kirk said:

When you click on Data, Sort and you select your column of dates do you tell Excel to treat numbers as numbers or text?

Not sure what you mean. I tried as you suggested and there was no selection for dates or numbers. Where should I look for this selection?

lr1277

victor2 said:

Are your dates entered as proper dates and not in plain text? There should be no need to copy the data to another sheet to sort it, and then copy it back. You can sort in situ.

When you sort, you should include the heading row in you selected data for sorting then make sure the "My data has headers" box is ticked in the sort options.

If you're worried about messing up your existing worksheet (which you do of course have a backup of), don't save it, or save it under another name while you play with it.

Not sure what you mean by proper dates. As I said, I changed the cell formatting to the 2 date formats I mentioned in my OP. Neither seemed to work.

Thanks, I found that header row box so am now using that.

Yes, saving the file under another name may be the way forward. Thanks.

lr1277

Carrot007 said:

Set the cell format to numeric temporarily to ensure they realy are dates (anything after the decimal is the time).

Anything not looking like a number then is not a date and needs to be fixed.

Thank you to Carrot007 and heedtheadvice. Sorry I haven't worked out how to quote multiple posts.

Yes I changed the column to numeric and the rogue dates are still showing  for example as "04/03/2021".

Do I just need to overwrite these cells with a date? Or is there another fix?