URGENT! Spreadsheet refuses to sort on a date field

[JohnWasilewski]

PlanMaker Professional 2021 (rev S1016.0624) 64bit

Please, SoftMaker, urgent help needed.

I have worked for several days on a text-processing spreadsheet. I am using a spreadsheet as a simple relational database because it is quick to set up and it has helpful functions for data validation and dropdown menus for data entry from related tables. The spreadsheet is intended as a chronology of events in some important research I am working on.

All the cells in one column are formatted as dates, using Category:Date/time, Cell Format:20/07/20

I'm nearly at the point where I need to start using the database for research and preparation for client meetings, and I have discovered, to my horror that it refuses to sort the data by rows to give ascending dates in the date-field column. When I select all the spreadsheet data and ask for an ascending sort on that column alone, it either does nothing or sorts incorrectly, giving me rows 'ordered' so that the dates are jumping up and down and all over the place.

This failure renders my 'chronology' spreadsheet completely useless.

As an experiment, I have also tried entering invalid dates in the date column.
It accepted these idiotic dates: 44/92/31 and 31/92/44

Please, SoftMaker, either advise me urgently how to sort my data by rows, with ascending dates in the date-formated column, or fix PlanMaker to make it do this properly for me!

My data file is confidential so I have emailed it to you privately.
Email sent date: 2020-07-20 at GMT 14:15 +0100.
---
John

[SuperTech]

I checked your file and found some problems:

1. You have 6 rows hidden above the top row due to which when you apply auto-filter that also applies to top hidden row. For this, you have to select the entire range (B7: J91) which you want to filter/Sort manually.

2. Many values in Date column are not correctly entered like in range B81 to B88 values are in format "00-07-04" which is incorrect. For these, you have to first correct these values so that sort can work properly. Otherwise, whatever you will enter PlanMaker will sort it accordingly.

I have sent you an updated sample file. Please check.

Thanks

[JohnWasilewski]

Thank you 'Super-Tech' for your SuperFast response. I really appreciate this. I was busy typing my own previous entry whilst you were replying. Would you mind having a look at what I was also writing, partially on the same points as you have mentioned. I had realised that incorrectly formatted dates were to blame, but I also discovered that some CORRECTLY-formatted dates were causing problems as well.
---
John

[SuperTech]

I checked it again in detail and didn't find any such issue. Please check my email and let me know with a screenshot which correct values are creating the problem in sorting.

Thanks

[JohnWasilewski]

HERE'S AN EXAMPLE.

This is an older version of my spreadsheet chronology database, with nearly everything stripped out, just to show what I discovered about date formatting.

Date format problem example.pmdx

(49.95 KiB) Downloaded 455 times

All the cells in the first column are formatted as 'DATE' cells.
All of them have been entered as legitimate versions of dates.

Yet some of them have become stored by PlanMaker as (I think) text cells.

This is not discoverable by the user from any indications that I can see anywhere on the sheet, except by accident, when a sort on the date field fails to work properly. And then it is a real headache to try to work out what is wrong!

I don't know how I managed to get into this mess. Quite probably I did something wrong when entering dates or maybe when setting the column to assign date format to all its cells. However, somehow, I did manage to mess it up like this, and PlanMaker LET ME DO THAT and then CONCEALED from me what was wrong.

When I first sent in my original cry for help, I had also done aomething else that was wroing, by intentionally using some invalid date formats (I did so for a reason, but it didn't work). This, you correctly picked up as what seemed like THE cause of the problem. Unfortunately, this turned out to be a distraction, which allowed the more difficult problem I am now referring to to remain hidden.

As I have suggested already, I think PlanMaker needs some more error traps to protect users from entering what look like dates into date-formatted fields, whilst PlanMaker is secretly storing them as text. It's easy for me to ask for this, and the program could of course be made to refuse to accept non-date entries into date-formatted cells but it is quite useful to be able to enter a text string sometimes, into a column that has been formatted as date cells, so I do not know what to suggest by way of a practical improvement. Could PlanMaker just issue a warning, maybe?
---
John

[SuperTech]

Thanks for sharing this short file for better understanding, Now please have a look in this file Column B and sorting problem.

Problematic range B20 to B25, just double click on any of these cells and you will catch the problem. While entering these dates, I don't know how you entered but you were able to enter one new line below date, Ctrl+Enter after the date value. Which messed up the dates and in this case PlanMaker has to consider them as Text.

I have corrected your file, removed extra new lines and sorted perfectly. Please remember careful data entry is a very important part here.

You can use conditional formatting for error prevention as I did in this example file.

Date format problem example.pmdx

(49.37 KiB) Downloaded 491 times

[JohnWasilewski]

I love your idea of conditional formatting to trap accidental text entry into date fields.
Really good idea.
Thank you.
---
John