Reputation: 239
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.
Upvotes: 4
Views: 109770
Reputation: 1421
Here's what you need to do:
Make sure the cells with the text dates are formatted as General.
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)
See also: Convert Dates Stored as text to Dates (Microsoft Office)
Upvotes: 9
Reputation: 49
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.
Upvotes: 2