Date formatting issue in Calc column

[Appalbarry]

My apology, I was sure that I asked this a couple of years ago, but it has re-appeared. I have a simple spreadsheet with:

DATE : VENDOR : ITEM : PRICE

All dates have been entered using the keypad as DD/MM/YYYY. Somehow Calc has varied the formatting from one cell to another.

I’m honestly not concerned with how it happened - I assume human error at some point. What I need to know is how to select the column and make the date format consistent all the way down.

Thus far I am totally baffled. I really just need a quick step-by-step instruction.

I use LibreOffice for everything, but once in a while it just stumps me. And yes, I’ve dug back into prior posts.

[robleyd]

The incorrectly formatted dates are left aligned which suggests they are text values rather than numerical values, which latter can be formatted in various date forms.

Converting Text to Numbers

[Appalbarry]

Thank you, but I’ve spent an hour on the linked page, and those that link from it; and experimenting with the sheet, and am no further ahead. If anyone can offer a clear Step #1; Step #2, Step #3 list of instructions, it would be wonderful.

[robleyd]

My apologies - that link isn’t what I thought it was.

Faq/Calc/How to convert number text to numeric data - The Document Foundation Wiki has an animation showing one solution.

[Appalbarry]

Aha! That did it.

1. Selected the column
2. Data : Text to Columns
3. Click “Standard” in the column header in the dialog box.
4. Column type: Date (DMY)
5. OK
   Now have:
   

[mikekaganski]

Note that your original screenshot shows that Calc was able to recognize your input as dates, where the first part of your AA/BB/YYYY (i.e., AA) was less than 13. In those cases, it converted the data to dates, and shown it in ISO format, right-aligned.

When the AA was 13 and greater, it couldn’t recognize it as date. This hints that in your (configured in LibreOffice) locale (or date acceptance patterns), the auto-accepted order is M/D/Y - likely not what you think it is. That would also mean, that you need to double-check those dates that were accepted initially - if they didn’t switch the order unexpectedly.