How to format date in libre office

Question

I have a column of dates in a format below:

Fri Dec 25 02:45:23 MYT 2015
Fri Dec 25 17:38:57 MYT 2015

How do I change it to a format as below:

2015-12-25
2015-12-25

I have tried to format cells but it is not working. Any idea ?

Answer 1

At first the Calc must accept those strings as dates. I don't know if any Asian language versions will do that. Default English and German language versions don't.

So at least with default English and German language versions, first there is a need to convert the strings into default date strings.

I've done this with the formula:

=--(LEFT(A1;11)&RIGHT(A1;4)&MID(A1;11;9))

Then applied the format YYYY-MM-DD HH:MM:SS on the cells.

Answer 2

Change column type when opening the file and try to format cells as any format you need.

Answer 3

Assume that dates are in column A.

1. Insert new column B to the left of old column B.
2. In B1, input: =Value(A1)
3. Select B1, right click -> format Cell -> Numbers -> date -> choose the required format
4. Apply this to all column B rows.
5. Select all of column B.
6. Copy all column B cells (with CTRL-C).
7. Select Edit from the menu.
8. Select Past Special from the menu.
9. In the dialog box that opens, unselect formulas, select the desired conditions, and press OK.
10. You can now delete column A.
11. Save ASAP.

Answer 4

If a cell is formatted as text (@) then everything you enter in the cell is treated as text.

You can eliminate all the apostrophes in those cells: Change the text format in the range to date format. with Find & Replace: Find: .* Replace: & In more options: mark selected range and regular expressions. Select the range if not selected. Replace All.

Answer 5

If the column is text... my LO version has the CALC function DATEVALUE [DATUMWERT in German], which converts a date text string to a date value