Category conversion in Calc

[bkpsusmitaa]

I have some cells with date in Text Format, because I imported them from a CSV text (rename the attached file test.odt to test.csv) file. (apologies for typos. Thank you, Lupp).

For example, a cell with text (Thanks, Lupp) “20-Jun-90”, which upon Right Click ⇨ Format Cells ⇨ “Numbers” Tab, in Unformatted Text.

How do I know?:
Explanation: Because, if I change the format using “Numbers” Tab ⇨ Category, from the pre-defined to “Date”, and juggle with the various Formats available in the “Numbers” Tab ⇨ various formats in the selectable “Format” drop-down textbox available, the displayed Cell-Value in Display Box inside the interactive Message Box doesn’t change to anything else.

Now, I used the concatenation to bring the text into “20/Jun/1990”. Code used: =CONCATENATE(MID(A3,1,2),"/",MID(A3,4,3),"/20",MID(A3,8,2))

However, still LibreOffice Calc doesn’t recognise those values as date values.

My question therefore is: How to help LibreOffice recognise those cells with values as "Number/Date"s?

If I instead write a value “20/06/1990” by myself and press “Enter” or “Tab” key, LibreOffice automatically recognises the value as a Number in Date format. How can I be certain?:
Because: Right Click ⇨ Format Cells ⇨ “Numbers” Tab ⇨ Category shows the number as “Date” format. Further, in the “Format” field drop down selectable various formats, I could switch between various formats and find them changed in the Cell Display Box in the “Format Cells” display in the interactive Message Box.

The videos would elaborate:
https://drive.google.com/open?id=1zDNfgSfe7GsMTnErozrB7gQ4yzUVHAhb

[Lupp]

To be certain if a cell content is text or number there are different ways.
-1- Use View > Value Highlighting.
-2- Do NOT set any horizontal alignment. Texts are displayed left aligned then, numbers right.
-3- Ask any cell by a formula like =TEXT(A5) or =NUMBER(A5) placed in another cell.

As already mentioned: No answer concerning formatting can help if a conversion is needed. (Don’t waste time with videos.)

[bkpsusmitaa]

Thank you, Lupp.

[Lupp]

If you actually have dates with (abbreviated) month-names, please provide a sufficiently “complete” example. What language are the names taken from? The recognition of dates given in such a text-form will depend on the language (locale). Months are named differently in different languages. "

[PYS]

Hi

No problem on import in my environment:

• if I check Detect special numbers

Or

• if I set the column type

HTH - Regards

[bkpsusmitaa]

Apologies, Pierre, for my unintended typos. The actual problem becomes difficult for me because of the format 20-Jun-90.
But I am now certain that you will solve this hurdle too. Because by your, and Lupp’s, trick, I could do it myself. Thanks.

[Lupp]

The file you provided does not contain dates in a DD-MMM-YY format (as expected based on your example) but in the (also silly) DD-MM-YY format (all digits, 2-digit-year).

A text can never be changed to a date in standard representation by setting a different format. The format codes you can use for dates only work with dates represented as numbers.

On import from a csv, however, you can enable the option Detect special numbers and select the appropriate Column type: to get the text-dates converted to numeric dates. Subsequently always set the only date format then which is internationally standardised: YYYY-MM-DD.

If the text-dates were already imported into a spreadsheet you can also use Text to Columns... to get offered the mentioned date recognition.

If you actually (also) have dates with months given in letters, any conversion may depend on locale settings. Locale probably Finnish in your case?

Editing:
The CONCATENATE() function and also the concatenation operator & return a text (sequence of characters). If this text is recognisable under the current locale of the spreadsheet, it can be converted using the DATEVALUE() function. The result may be in the default format for integer numbers, but now you can apply any Numbers format code to it.