Conversion of text date to numeric - conversion errors, problems and bugs

English
, , ,
#1

(These two files were attached by the OQ on request. For unknown reasons the process was not quite successfull. I only am editing this post to make accessible both these files here. Lupp)
DateFormatErrors.odt
AnUnnamedCalcFile.ods
(Another attachment was added later. Again I clean it up a bit. Lupp)
AgainDateFormatProblems.doc

I have a problem converting text to dates in Writer for sorting. The background is that I had a large (83 pages) table in Word which I have transferred to Libre Writer. I sort this table in date sequence as well as other sorts. The date is entered as text, not number, as mm/yy eg 10/15 for Oct 2015. This sort worked in Word, sorting in year sequence but it doesn’t in Writer. I have tried converting these dates to number and date format in Writer but the conversion raises several problems before even attempting to sort. (See attached examples).

As can be seen, I have entered some random dates (in MM/YY format) in the first column. In the second column I have entered the same dates but formatted them as mm/yy. The first problem - all post 2012 dates convert to 12/99 as can be seen.

I then used a simple formulae in the next column again converting to mm/yy format. Again all post 2012 dates convert to 12/99. However, pre 2012 dates seem to be assumed to be in dd/mm format and assumes 2016, again giving totally the wrong answer.

Obviously, none of this enables correct sorting! In trying to sort, I also note another problem in that 2010 sorts before 2002 to 2012!

Trying to do the same thing in Calc produces similar problems but I’d far rather just solve the problems in Writer.

Have I discovered some software bugs? Or am I missing something obvious?

Any help would be gratefully appreciated as I don’t want to go through a mammoth exercise of re-entering all the dates.

Many thanks

#2

@terry3 10k: There was Karma raining.
You did not discover any bug except the one that people are using silly date formats. Dates conforming to ISO 8601 (2016-04-11 for today e.g.) are the only ones not causing nasty problems. again and again.
I do not know how MS Word is circumventing this, but I feel sure it will cause problems in another place as most silly smart features do. At least it will again be heavily locale dependent. Use 2016-04 (YYYY-MM) if you “need” to omit DD.

#3

Hi lupp, Thanks for the response. Not sure what this is about sill date formats; I mean, how sillier can you get than year first, far more logical and sensible to have form DD/MM/YYYY!!!

In terms of what I used, there is no problem entering dates formatted differently from now , it’s hundreds of dates entered over the last 10 years or so I need to change and all I need is month and year so why (historically) should I have entered day when MM/YY worked fine? Still need some help please

#4

DD/MM/YYYY is better than MM/DD and first of all better than 2-digit-years. It isn’t the final step, and those having negotiated and fixed the ISO standards are clever guys we all pay for. With respect to your problem: Lexicographic sorting of ‘Western’ texts (left-to-right, alphabets) always assumes the most significant information leftmost in the text. If we regard “More significant left” and “Fix number of digits” lexicographic and numeric sorting will concur. Logical? Functional!

#5

Whatever the logic of date formats, I would still be very grateful if someone could help me solve the issues I have in Writer (and/or Calc) so any constructive advice will be more than welcome. As I said, I’ve hundreds of dates to change that have been entered over the years but, obviously, the problem can be got round with new entries. (But the sort did work in Word - & yes, I was surprised when I tried it as I hadn’t anticipated wanting to sort that column)

#6

I surely try to be constructive. The sample you provided meanwhile, however, does not much help me to help. Under common headers I find very different content. Below ‘Date copied as text from Writer’ e.g. I also find numeric values supposedly moved wrongly to the current year. Please supply a relevent sample of your original data (in ‘Writer’), confidential parts removed/replaced where appropriate.

#7

Hi Lupp, many thanks you comments etc so far. I have attached an extract of the original file saved as .doc in Writer but before saved as .odt. (It was set up for an investment club; have removed all the comments but not touched dates etc.). As I said, I would be very grateful of any help you can give.

#8

How should I provide further help?
I will try once more with the help of some explanations inserted into the new documents I attach to my answer.

#9

Cracked it! Lupp, many thanks indeed for your explanations and formulae; I certainly could not have solved my problems without your knowledge of functions but I admit I should have looked more closely at your first two attachements as I now undestand them. My table now sorts well in any sort required. Thanks again.

#10

moshe11, thanks for your suggestion. In practice the problem starts when copying from Writer and pasting into calc when trying ‘your’ way. As the data is pasted, Calc automaticall converts to number (date) format but gets it wrong so the conversion has to be done in a indirect way such as Lupp’s method which worked and was relatively easy (once I’d got my head round it!).

#11

The attempts made in the text-tables inside the .odt attached to the question won’t get us anywhere. The supply of functions and operators usable in text tables is very small and rarely of actual use. Historically this may go back to the fact that the “vendor” as well of ‘Word’ as of ‘Excel’ as two different “products” wanted to not make obsolete ‘Excel’ by the capabilities of ‘Word’ for a huge part of the customers.

I still insist on my assessment that a conscious decision about the data formats to use is a neccesary part of preparing tables not just made to be pretty, but also to provide functionality. This in significant cases not only when using spredsheets, but also when using text-tables.

There may be (hopefully!) a more concise solution. I cannot test with any version of ‘Word’ but if it actually can do the trick, someone relying on it should use ‘Word’. What I can get using ‘Writer’ and ‘Calc’ is by far too complicated in my eyes. Just for the judgement by others I attach the two files here.
StrangeDatesToSort.odt
HelperSheet.ods

Editing 2016-04-12 20:25
Additional attachments as advertised in my comment above.
ask68042AgainDateFormatProblemsReworked.odt
ask68042AgainDateFormatProblemsReworkedConvertedToSpreadsheet.ods

#12

I think you can use a different way .

  1. Open the data in Calc , then break the current “date-text” into 2 columns
    by using the slash symbol as the breaking point .

2 ) Now you can sort the year as main sort , and the month as secondary sort .

If there are still problems :

  1. you can convert these new 2 columns fron text to numbers , and then step 2 .

In case of more problems :

  1. Join the 2 columns to create a date (don’t forget to add 1 as the day) .

Now you have a full and legitimate date that any software will recognize .

Hope this will solve your problem . And if you need more help , just ask .

#13

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.