Table Format for Sort by date?

[withoutFeathers]

Hi,

I'm setting up a table that I'll want to sort the rows by date.

So far I haven't figured out a date format that the sort function will recognize accurately to get them in order, though I'm still trying things.

I'm using a Macro for table sorting that was supplied to me years ago by Nisus (for NWP version 2, which I still use), "Sort Table By Selected Column", which I presume uses their standard sorting function.

Does anyone know of a date format that might work?

Thanks

wF

[withoutFeathers]

OK, I can answer my question now.

Format [YYYY], [MM], [DD], in numbers, seems to work. I.e.
{2019, 02, 02} = February 2nd, 2019, and if all the others are in this format it sorts correctly in chronology.

wF

[martin]

I'm glad you figured something out As you discovered, using a normalized fixed-width date format where the most significant numeric date components appear first is the correct solution.

I wonder if there's a way we can improve Nisus Writer here. It would be nice if we automatically detected dates, or various other kinds of specialized data formats in the table column being sorted. Nisus Writer already does this for numeric values. Or maybe we should just offer a list of sorting options like BBEdit does for paragraphs. They have a very nice "sort using pattern" option, for those comfortable using regular expressions.

[withoutFeathers]

Or maybe we should just offer a list of sorting options like BBEdit does for paragraphs. They have a very nice "sort using pattern" option, for those comfortable using regular expressions.

I can see that the coding for mixed written/number dates might be difficult, but I have to count myself as someone who has never become comfortable with Regular Expressions.

I'd plead for Nisus to recognize -- either automatically or by a popup choice -- at least one standard long date format, ie:
August 27, 2019 = [Month name] [day,] [year] .

The advantage of this over the numeric ones is that it's human-readable and understandable at a glance. That's not true for the standard all number ones, even with practice, because day and month order are not standardized across countries, so that one really has no idea what this means:
08/04/2019
It could be August the fourth or April the 8th. No way to know unless you know the country it's intended for and what system they use.

wF

[martin]

I'd plead for Nisus to recognize -- either automatically or by a popup choice -- at least one standard long date format

That sounds like a good idea. It would be best if Nisus Writer could detect (or ask about) different date formats. Even if a person is comfortable with regex it wouldn't solve this particular task, since captured month names would still sort alphabetically.

Thanks for your feedback!

[johseb]

A viable solution is to use a spreadsheet to create the table content (maybe copying form an external source), manipulate it as needed and then copy to NWP.
It works quite well using Excel; a table is automatically created on pasting in NWP (even though with invisible edges that you then need to modify).

Of course it depends on how dynamical your table is; if it changes a lot you need to keep a live version in the spreadsheet and copy only at the end of the document creation (or from time to time, which admittedly is inconvenient, if you want to have it visible in NWP).

[johseb]

A different approach probably better suited for the specific OP need could be:

1. Develop your table in NWP as you did using ISO dates (YYYY-MM-DD) that allows sorting
2. At the end of the work select and copy the date column of NWP table
3. Paste in a spreadsheet and change the format to something more friendly e.g. Friday August 31, 2019. Copy the modified spreadsheet column.
4. Back in NWP, select the date column (it should still be selected from step 2) and use the command Paste Text Only.

[withoutFeathers]

3. Paste in a spreadsheet and change the format to something more friendly

Nice.
Though I no longer have a viable spreadsheet, but maybe I can rustle up something out there in the cloud that will do it as a service.

Unless Apple supplies one in the macOS that I don't know about... ?
Anybody know?

wF

[adryan]

G'day, wF et al

Apple's Numbers is a free and very powerful spreadsheet application, available for both macOS and iOS.

But one does not want to resort to another application for this purpose.

Here are two macros that operate on selections. One converts US dates to ISO 8601 format (viz, YYYY-MM-DD); the other does the inverse conversion.

Date US → ISO.nwm

(21.82 KiB) Downloaded 748 times

Date ISO → US.nwm

(22.69 KiB) Downloaded 752 times

There are many advantages of the ISO 8601 date and time format.

https://www.cl.cam.ac.uk/~mgk25/iso-time.html

I use it almost exclusively. It’s particularly useful in filenames. On stationery I include an interpretative guide:–

ISO Date.jpg (30.56 KiB) Viewed 21270 times

Cheers,
Adrian

[phspaelti]

Adrian's macro is nice, but will not work properly, if the date has the form "January 2nd, 2019". Here's a different macro which may succeed in more cases, but has other drawbacks.

[withoutFeathers]

Adrian's macro is nice, but will not work properly, if the date has the form "January 2nd, 2019". Here's a different macro which may succeed in more cases, but has other drawbacks.

Thank you both; I may work out a system that uses one or more of them.

@phspaelti, yours even does a correct job on things like "Aug 29 / 19", with or without the slash. Quite amazing.

So, what are the "other drawbacks" that you speak of for this one?

wF

[phspaelti]

So, what are the "other drawbacks" that you speak of for this one?

Since Adrian's macro uses Find/Replace it affects only the date parts of your selection, and leaves the rest intact. The macro I provided will, if given a selection that contains non-date parts, might end up deleting/overwriting those. If you use it on a table column where all cells contain a date and nothing else, it should work fine. But if the cells contain other text, with or without a date, you might get some bad effects.

To accomplish what you originally set out to do, I would write a macro that worked like this:

1. add a column to the table
2. populate the column with ISO dates matching the dates in the table
3. sort
4. remove the added column with the ISO dates

But as always with such tasks you need to make some decisions first: Do you want a "fool-proof" macro that works correctly for every eventuality, or just one that's good enough for my special case? The first is usually quite time-consuming. I tend to prefer the latter. But for that you need to know more specifically what case(s) you want to cover.

[adryan]

G'day, Philip, wF et al

I knew there'd be some way to get the macro language to do this. Thanks to Philip for showing how it's done.

All sorts of variants are possible here. I guess my approach would be to decide on my own immediate and anticipated needs, and then devise a fairly general solution that will suit me into the foreseeable future.

For example, my macros will give correct results if a date is immediately followed by "ff" or an asterix or the like, but not if it is followed by something like "-17" or "/21"; Philip's strips out all such additions. However, Philip's deals successfully with ordinal numbers, while mine do not. Mine deals with all dates in a selection and leaves formatting unaltered, whereas Philip's does not (but I've no doubt this is easily accommodated if needed). Philip’s point is well made: it becomes a question of the sorts of date formats you use and the way you want to interact with them.

I am sure both methods can be modified to do what is required. The question is: What is required?

Now that the issue of ordinal dates has been raised, it's not enough to strip out the ordinal suffixes when converting to ISO format. You probably want to (re)instate them when doing the inverse operation. I could do this, but I don't use them.

Cheers,
Adrian

[phspaelti]

Now that the issue of ordinal dates has been raised, it's not enough to strip out the ordinal suffixes when converting to ISO format. You probably want to (re)instate them when doing the inverse operation.

Except of course that once you have converted them you won't know if the original dates had ordinals or not. It's for that reason that I recommended a macro flow that adds a column and then deletes it. Essentially the motto is "don't modify the contents of the file".

So here is a macro in line with what I proposed earlier. It should order the table correctly, provided the selected cell (or cells) have dates in them.

*Macro replaced with updated version. See below*

[adryan]

G'day, Philip et al

I think that does everything that's required for wF's purpose, Philip, as long as every cell in the selected column actually contains something from which a date can be extracted.

Perhaps I should clarify some things about my approach.

Although wF is dealing with a table, I sought a method that did not restrict itself to Tables. One might, for example, have paragraphs beginning with dates followed by a colon, a space, a tab, whatever. Once you've converted the date format to ISO, it's easy to use Edit > Transform Paragraphs > Sort (Ascending/Descending…) to sort the paragraphs according to date. You would then convert the date formats back to the original format.

The macros I submitted work with dates wherever they are located within a chunk of text. And that text can be an "ordinary" paragraph, a Table or a List. I submitted them separately because they may have utility independent of the current situation. But it's easy enough to combine them into a single macro along with the desired sorting command.

(Aside: Note that List Items are sorted according to their actual content. That is, the List Item Number in a Numbered List is ignored for the purposes of sorting. Consequently, the List Item Numbers can end up associated with different content from previously, although they themselves will remain in numeric order. This is generally how you want things to work.)

The sticking point with my approach concerns the range of date formats you might be using; in particular, ones employing ordinal numbers. But, as I said, it's easy enough to treat them if needed. I've amended my Date US → ISO macro so that the day of the month is allowed to be expressed as an ordinal.

Date US → ISO.nwm

(22.15 KiB) Downloaded 739 times

And I now offer two versions of the inverse conversion, one for ordinals and the other not.

Date ISO → US.nwm

(22.81 KiB) Downloaded 745 times

Date ISO → US ordinal.nwm

(23.1 KiB) Downloaded 727 times

It's easy to see what bits of these macros would need to be modified if you were using a language other than English. (I don't know how the macro language deals with extracting date components in other languages.)

Other variants include:–

December 21/22, 2019
December 21st/22nd, 2019
December 21-22, 2019
December 21st-22nd, 2019
and cases where en dashes are used instead of hyphens.

But I haven't dealt with them here.

Cheers,
Adrian