Forum Discussion
Dates not Sorting
Hi I have attached an image, I am using the sort filter to put the dates in order and as you can see at the top three of the rows are not filtering correctly and just staying at the top, I wondered if anyone knows why this is happening.
Hi FinAndo12
In the picture you shared the dates are left aligned. This usually means they are Text values, hence they don't sort as you expect
Assuming your dates are in Column A, starting in Row 2:
- In B2 enter: =DATEVALUE(A2)
- Copy the formula down as necessary
- Select B2:Bxxx > Ctrl+C
- Select B2 > Right-click > Paste Special > Values
- Delete column A
- Apply the sorting on the new Column A
- Hi Lorenzo,
Just to clarify so I put that formula in for me it would be =datevalue(c2) would go into the cell d2, then do i copy that formula into every cell in the new column?
and then do I have to go down every cell to do the copy and then paste special values, and then delete the old column and apply the new sorting?
The dates are left-aligned. What happens if you select column C and set the horizontal alignment to General? I suspect that the first three will become right-aligned, while the others remain left-aligned. Is that correct?
- Hi, Yes that is correct
Select from C5 down.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited, click Next > and then Next > again.
Select DMY from the date dropdown.
Click OK.
The values should now be right-aligned, and sort correctly.
