Exported date format in excel sheet but it won't recognise as a date value in excel cell.

[aligahk06]

Dear All,

The following date exported from application in excel sheet but cell won't recognise as a date value. format is MM/DD/YYYY HH:MM:SS AM or MM/DD/YYYY HH:MM:SS PM.

How to make it correct date value in excel cell.

9/28/2014 11:57:00 AM​

11/14/2012 11:32:00 AM​

4/3/2014 11:27:00 AM​

9/9/2013 1:21:00 PM​

5/4/2015 12:50:00 PM​

9/7/2014 2:57:00 PM​

3/25/2014 2:15:00 PM​

1/16/2012 4:39:00 PM​

11/4/2014 12:07:00 PM​

5/26/2013 3:13:00 PM​

7/27/2015 10:29:00 AM​

 

[aligahk06]

Attach excel sheet

 

[p45cal]

Normally I'd say use Text-To-Columns and specify the date locale at the end, but in this case it doesn't work well because there is time information in each cell too which messes it up. You could do it using text-to-columns 2 or 3 times in succession but it's a pain.

You could use a formula approach; this formula looks at cell A2 so you need to put this formula somewhere in row 2 and copy down and across:

=DATE(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,FIND(" ",A2)-FIND("/",A2,FIND("/",A2)+1)-1),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1))+TIMEVALUE(MID(A2,FIND(" ",A2)+1,99))

ugh!.
There is such a formula in the Mobily Formula sheet. You'll want to copy/paste-special|Values probably.

You could use a macro which will convert the cells in situ (and you may need to format the cells to see the date/time as you want). The attached contains a copy of your sheet called Mobily Macro with a button which you click after selecting the cells you want to convert, which runs this macro:

Sub blah()
For Each cll In Selection.Cells
  a = Split(cll.Value, " ")
  b = Split(a(0), "/")
  cll.Value = DateSerial(b(2), b(0), b(1)) + CDate(Join(Array(a(1), a(2)), " "))
Next cll
End Sub

Or you could use Power Query which is the most robust solution.
The attached has a query which produces the table on sheet Mobily PQ which was produced by naming your dates range myRng and changing the Type of both columns to DateTime using Locale and choosing English (US). You can disconnect this table from the query if you want, and convert it from a table into a plain range.

ps. It'd be nice if you said thank you from time to time, we're only humans, not robots.
[seems I broke my promise at http://www.vbaexpress.com/forum/sho...on-employee-id&p=394160&viewfull=1#post394160]

 

[p45cal]

EFFING cross posted AGAIN without telling us!

Date Unable to recognise in excel sheet

Dear All, The following date exported from application in excel sheet but cell won't recognise as a date value. format is MM/DD/YYYY HH:MM:SS AM or MM/DD/YYYY HH:MM:SS PM. How to make it correct date value in excel cell. 9/28/2014 11:57:00 AM 11/14/2012 11:32:00 AM 4/3/2014 11:27:00...

www.excelforum.com

No more help from me aligahk06, EVER.