Macro or formula on Excel to Standardize dates

Question

I really need help on this. I've been looking for an answer for a LONG time but couldn't find it. In my work, I need to sort a table on Excel using the date present on every line. The problem is that the spreadsheet I use have different layouts of dates as seen below.

2/13/2017 4:43:02 AM (M/DD/YYYY)
02/11/2017 05:05 (DD/MM/YYYY)

I can't sort it this way because it always read wrongly. What I need to do is to split cells and then concatenate them but this is causing a lot of trouble.

Could you help me check if there's any way to do it automatically, using a macro, or at least using just a formula?

Answer 1

This is happening because the output date you got is in text format, not in date format. Here is the trick to resolve your case to get the output in mm/dd/yyyy hh:mm format. You can change the format to your desired one.

If text date is in A column, then formula is -

=DATE(MID(A3,SEARCH("/",$A3,SEARCH("/",$A3,1)+1)+1,4),LEFT(A3,SEARCH("/",A3,1)-1),MID($A3,SEARCH("/",$A3,1)+1,SEARCH("/",$A3,SEARCH("/",$A3,1)+1)-SEARCH("/",$A3,1)-1))+TIME(HOUR(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,1))),MINUTE(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,1))),SECOND(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,1))))

Hope this helps. Rate if satisfied. :)