You will have to trace back to your source data. There is no way Excel itself knows whether 1/2/2014 for example should be the first of February or the second of January, only that it is either 41671 or 41641.
Edit In your second example, clearly 28/9/2013 17:59 is September 28. If 10/01/13 12:11:00 PM had the same formatting (perhaps came from the same file) then it is January 10. But if the formatting was different then it could be October 1. If you are seeing AMs and PMs with formatting as dd/mm/yy hh:mm;@ then some of your data is text and there is no reliable 'automatic' way to switch this to a date/time serial number without knowing the text convention (ie whether DMY or MDY), hence the need to revert to source.
Obviously 'day' values greater than 12 are actually months but that does not help much when for less than 13 it depends upon the formatting.
In addition, given your various sources, there is a risk that both the 1900 and the 1904 conventions might have been used and even possibly others also (your data logger might be on UNIX time, which starts in 1970).
+ 1 Technically you are right. Excel can not know. But if the user forcibly wants to convert to a specific format by splitting then that is possible :)
+1 @pnuts you have a point. That was my gut reaction, too, when I read the question. "Does the OP REALLY know which date is supposed to be DD-MM-YYYY versus MM-DD-YYYY if they only see the date and the first two items are both less than 13. It would not surprise me if the OP comes back to redact. But +1 to Sid, too for his approach.
@pnuts, thing is, once the data is inside Excel, Excel will have done its thing on interpreting it and has made up its mind about DMY or MDY. Much easier would be to import the data with the wizard and TELL Excel if the dates are DMY or MYD.
@SiddharthRout, how can I NOT luv ya after you are passing that quote back to me? And, of course, I totally agree that users don't necessarily enter data into Excel to promote easy processing. I so wish users would all learn how to operate the tools they are using every day and make their lives easier. Spend five minutes on learning a few basics every day and remove hours and days of time wasted on troubleshooting. - I think this conversation should move to Meta.
@pnuts, I asked this question years ago in another forum: excelforum.com/the-water-cooler/ -- most folks involved in the discussion are now MS MVPs, but were not at the time.