I received an Excel file with a column filled with slash-separated dates in a given column.

The dates isn't directly recognized by Excel as being so. Thus, it is not sorted correctly when I use the sort tool. I am able to turn this cell into date format by going into it, hit F2 to edit, then just Enter. But this is too long because my file have hundreds of lines...

When I use the format dialog, date separator are either - or .. Is it possible to use a custom format to turn my column into an accepted date format? Formatting Excel slash-separated dates

I saw here I could use a formula in a collated cell to do the job.

I'm looking for the most efficient way to turn this column into a date format, that would avoid using vba, if there is any other as the above.

Edit: I'd rather avoid changing my PC's behavior, so I'd also prefer not to change the locale like advised there of the whole system. Maybe changing it only for a workbook could work? But how?

  • Using the formulat in another column is probably the easiest way – Máté Juhász May 29 '17 at 6:54
  • Use US locale or custom format maybe? – eckes May 29 '17 at 6:55
  • Try pressing "CTRL+H" and replacing "/" with a "." and then reformatting the cells to a date. If this does not work then you can use the "DATEVALUE" function. – Kevin Anthony Oppegaard Rose May 29 '17 at 7:17

Thanks to Kevin, replacing the / with . did the trick.

To avoid replacing every / in my sheet, I had to format the column with a specific color and use the find with specific format option:

find with specific format

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.