0

I am met with very strange things with spreadsheets in Excel now. Some cells with a date like 2023-10-22 can not be sorted with other cells in the same format in a column. I changed the format of the cells to General or Text, but neither works. The strangest thing is, when I copied other cells that can be sorted to the cells not sorted, if I do not modify the cell date, then it works. But if I modify the cell date, then it can not be sorted.

I have checked the content of the cell by copying and pasting it by value, which showed 45221. It seems that the value of the cell is not correct. The sample of the spreadsheet can be accessed here.

Cells with digits like 45221 can not be sorted. If I enter dates like 2023-10-22 manually in column B, it still can not be sorted and the paste-by-value shows 45221. The display of column B is set to General.

After changing fx in cells of column B to the format like ="2023-06-03", paste-by-value shows the correct date format and can be sorted.

Improve this question
9
  • That is odd. It should show 44858 which is the number of days since 1/1/1900. In any event, you may have a mix of "real dates" and text strings that are not real dates to explain your issue. If you want more assistance, you will need to provide sufficient data so that your problem can be reproduced.
    – Ron Rosenfeld
    Commented Mar 13 at 11:24
  • It should be 2023-10-22. The strangest thing is that even if I deleted the cell and recreated a new one, it is still the same. The spreadsheet is generated by c# program. I think that causes the problem.
    – E Zhang
    Commented Mar 13 at 16:14
  • As I wrote, you have not supplied sufficient information to give further advice.
    – Ron Rosenfeld
    Commented Mar 13 at 16:47
  • What other info do you mean?
    – E Zhang
    Commented Mar 13 at 17:32
  • For a start, what does your worksheet look like? How did the data get into the cells? How does the data appear in the cell, and also in the formula bar? What does your sheet look like if you format the column of dates as General? Do they all look the same or are some numbers and others look like dates? What are your windows regional settings? Are you using a Mac or a PC? Screenshots may be useful.
    – Ron Rosenfeld
    Commented Mar 13 at 18:28

2 Answers 2

Reset to default
1

Dates are stored as numbers. The number tells how many days has passed from a specific "base date"[1], the decimal portion of the number specifies the time inside a day.

If a cell in a list or range does not change as you sort the list/range; the cell is likely to contain a dynamic formula that causes the same result regardless of the sorting, i.e. it has it's main references reaching outside of the sorted list/range.

[1] Enter a zero in a cell, and tell Excel that it is a date by use of "Format cell" [CTRL+1] and you will see the base date. This date may differ depending on legacy and settings.

Improve this answer
0

I am still confused about this. It seems having a problem of entering a string format as Date in an Excel cell. But I find an alternative by entering a date as ="2023-10-22", which can be sorted correctly.

Improve this answer

You must log in to answer this question.

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