Here is my test report. It's complicated but was certainly worthwhile doing.
Thank you Philip and Adrian for those macros. Here goes:
I used the revised versions of three of the macros that you revised, and called them "Dr2" in my naming.
I ended up with 6 distinct macros to test, which I prefixed 1-6; specifically the resulting macro file names were:
- 1- Date To ISO (Philip)
2- Date US → ISO-Dr2 (Adrian)
3- Date ISO → US-Dr2 (Adrian)
4- Date ISO → US ordinal (Adrian)
5- Sort Table Rows By Selected Date Column-Dr2 (Philip)
6- Sort by Date US (Adrian)
So I had three types of transformations being tested:
1,2 are Date to ISO (one each Philip and Adrian)
3,4 are ISO to Date (both Adrian)
5,6 are Sort column by date. (one each Philip and Adrian)
How I Tested:
I used a single Test table with about 30 records. I pasted in a fresh copy for each of the 6 macros to work on.
This test table was pre-existing and has various challenges, including:
-- some cells have only a year date like '2003'
-- there are blank rows (and so blank cells in the Date column)
-- dates are not all US standard format (lots of variety, like 'Aug 6 / 19', etc.)
-- there's a top row of titles of the columns that shouldn't sort
Later I'll paste in my full test notes for each, but here's the summary:
Conclusions:
#1, Works, except for blank cells (which it creates date for) and short dates (‘2003’) which it creates day and month for. Needs minor debugging.
#2, Mostly doesn’t work, since I’m not using US pure date formats.
#3. Works all.
#4. Works except errors in some abbreviations (July 12nd, 1977, etc.). Needs minor debugging.
#5. Works all. Feature request: choice of ascending or descending.
#6. Doesn’t work directly on multi-format dates. On US dates, changes to ISO and then gets stuck without sorting. Can be sorted afterwards. Needs some debugging.
My fuller test sequence for each is below.
Test Date Macro #1:
1. Select “Date Submitted” column cells, excluding title row, but including blank cells.
2. Choose Macro #1.
Result:
All correct except anomalies:
— Blank cell changes to “2000-01-01” instead of staying blank.
— Truncated date with only year (ie., ‘2003’) changes to correct year but with addition of today’s date (i.e. becomes ‘2003-09-01’) instead of just staying ‘2003’.
Test Date Macro #2:
1. Select “Date Submitted” column cells, excluding title row, but including blank cells.
2. Choose Macro #2.
Result:
Almost all don’t work out of 20 different rows in table. Only pure US dates were changed, ie, ‘July 12, 1977’ worked; but all others with truncations or slashes etc. were ignored or partially changed incorrectly. Unfortunately I’m likely to vary in my date formats greatly and so this is not usable in this circumstance.
Test Date Macro #3
Method:
Create ISO by using Date #1 Macro on the “Date Submitted” column, including blank cells.
— Then attempt to reverse this to US date format with Date #3
Result:
Perfect reversal of all ISO to U.S. date formats, including skipping blank cells and leaving truncated (‘1986’) dates as they are.
Test Date Macro #4
Method:
Create ISO by using Date #1 Macro on the “Date Submitted” column, including blank cells.
— Then attempt to reverse this to US ordinal date format with Date #4
Result:
— Numbers are correct, but several ordinal abbreviations are rendered incorrectly:
July 12nd, 1977
April 12nd, 2018
October 12nd, 2018
December 13rd, 2018
September 12nd, 2018
Test Date Macro #5. Sort Entire table by date column.
Method: Choose column and apply Macro.
Result: Worked!
—one anomaly: empty rows are sorted to between the ‘1986’ date and the ‘2003’ date. This is probably because (see anomaly in Test #1) blank cells are being read as if they are 2000-01-01.
One request: it would be nice to have ‘ascending’ and ‘descending’ choice box, like in regular table sorts.
Test Date Macro #6. Sort Entire table by date column.
Method: Choose column and apply Macro.
Result: Macro gives error in ‘line 6’. Cancelled.
Revise: This Macro is for U.S. dates, so fair thing to do is transform the dates to U.S. date first, by using sequence Date Macro #1 (to ISO) -> Date Macro #3 (to US from ISO) -> Date Macro #6 (sort column).
Result: First two steps done OK (blank row blanked again, correcting anomaly in Macro #1), then Macro 6:
— error in line 28
Try by selecting all relevant rows, rather than just cursor insertion in column:
— changed all the dates to ISO, AND then gave error.
— However I successfully sorted now on ISO date (using other Macro, regular table sorting)
However this is a longer way around than doing it manually with the other macros, so needs work.
That's all for now!
I need to go for a walk...
wf