How do I correctly parse a range of dates?

[mmasroorali]

Please see the followong excerpt from a calc file. It has around four hundred entries. I got these entered by a data entry operator using Excel in another machine. Now this file has been saved as .ods in my machine.

The first column contains serial numbers. The second column contains dates. The second column entries need to be interpreted as, D/M/Y. However, if I set the column format as Date, those with a number in the range (1-12) before the first /, are being parsed as M/D/Y (102-106, 109-111). And those with a number greater than 12 before the first /, remain unparsed (107,108).

I want the second column to be parsed as D/M/Y. So, the first one with serial 102 should be parsed as Nov 11 2013, the third one with serial 104 should be parsed as Nov 6 2013, and the one with serial 107 should be parsed as Nov 13 2013. As you can see, the sheer volume makes any manual operation prohibitive.

Do you think you can point me to some function, or a sequence of operations to correctly parse the date texts?

Thanks in advance.

[Pedro1]

You can do this with a function but it is probably faster to just replace the date separator: Edit > Find & Replace > Search for / and Replace with (whatever is the date separator in your country) Example: Replace with - (dash)

Hope this helps…