Convert text YYYY-MM-DD HH:MM:SS to Excel date

Question

I have a spreadsheet in Excel where in column, for example, B at the cell B5 and down there is a date entered in text format as YYYY-MM-DD HH:MM:SS. I want to convert it in a such way that Excel will be able to recognize it as date, because then I need to use filters and so on.

I found this question and tried to do it as follows: go to Format Cells, select Date Category, change my locale to Australia, select YYYY-MM-DD, but it doesn't work.

Can anybody help me to convert all text dates in a selected column to Excel dates? Hours, minutes and seconds aren't very important.

Answer 1

Here's what you need to do:

1. Make sure the cells with the text dates are formatted as General.

2. Use the DATEVALUE function to convert the text dates to Excel serial numbers. For example, if B5 has a YYYY-MM-DD HH:MM:SS, then in cell D5 type:

=DATEVALUE(B5)

3. Format cell D5 as a Date.

See also: Convert Dates Stored as text to Dates (Microsoft Office)

Answer 2

since date format doesn't work, please try

right click>> format cells>>Category= custom>> type=d/mm/yyyy h:mm If you do not want h:mm then just use type=d/mm/yyyy

Let me know how it goes.