Ask Your Question
1

Sort by Date Options (using the AutoFilter)

asked 2019-12-17 22:03:42 +0200

whocan gravatar image

updated 2020-07-31 00:28:16 +0200

Lupp gravatar image

LibreOffice 6.3.3.2 (64x) When trying to Sort by Date (column header with cells formatted mm/dd/yyyy), the selection options are not by dates but digits. Any idea why and how to get the date to show as an option? This actually started about three or for updates ago, maybe v6.2. See image. The sort box is open with options and the cells are visible at the very bottom.

Admin Followup: Thank you. You actually hit the nail on the head. The number "223" is not a typo. Dates are being returned as numbers in the sort box, e.g.: 2017=223, as in the photo. Each year increases sequentially: 2017=223,2018=224, 2019=225, etc.. All the months = 15, regardless of the year. The data is entered correctly as mm/dd/yyyy. As such, nothing happens when pushing the OK button because the cells' data does not include "223."

I should also mention I've removed the headers and reapplied, removed the headers, saved the file, reopened and reapplied the headers and I've exported the data as cvs, imported the data into a new file and the problem exists in all circumstances.

DISREGARD! There are 6 cells with 0223 as the year. Interestingly, the data was changed during an update or a grab and expand. Or not. I am sufficiently embarrassed. Thanks for your patience and support.

Attached is the spreadsheet with personal data removed and but with the "Date" column filled.C:\fakepath\CalcSortSampleData.ods

image description

edit retag flag offensive close merge delete

Comments

A screenshot doesn't provide the required information (real data, formats) to analyze your issue. Please upload a sample file showing the problem (but obviously you have one date which is year 223 and could be a typo)

Please do not use Add Answer but edit your original question to enhance the details of your question. Thanks in advance …

Opaque gravatar imageOpaque ( 2019-12-17 22:21:55 +0200 )edit
1

DISREGARD! There are 6 cells with 0223 as the year

Looks like simply a typo while entering "2023" -> "0223"

Mike Kaganski gravatar imageMike Kaganski ( 2019-12-18 07:28:23 +0200 )edit
1

Based on the answer by @LeroyG :
The issue was none. Whether or not the cause were typos: The entered dates were as shown by the AutoFilter.
In addition: The AutoFilter doesn't offer to sort the data, but has an option to choose in what way it should list them in its dropdown area. .

Lupp gravatar imageLupp ( 2020-07-31 00:36:36 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-07-30 22:26:14 +0200

LeroyG gravatar image

There are 188 cells with year's numbers twisted.

Select the Date column, and make a Find & Replace using the values /022 in Find:, and /202 in Replace:.

Re-select the Date column, and make a new Find & Replace using the values /023 in Find:, and /203 in Replace:.

After that the AutoFilter works as expected.

*Check the mark (Correct answer mark

src="/upfiles/15937350258938472.png">) to the left of the answer that solves your

question.*

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-12-17 22:03:42 +0200

Seen: 146 times

Last updated: Jul 31