Ask Your Question

How do I correctly parse a range of dates? [closed]

asked 2013-11-26 15:09:04 +0200

mmasroorali gravatar image

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.

image description

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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-14 23:01:02.508011

2 Answers

Sort by » oldest newest most voted

answered 2013-11-26 16:20:11 +0200

Pedro gravatar image

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...

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-11-26 15:09:04 +0200

Seen: 431 times

Last updated: Nov 26 '13