Excel won't recognise as date ( mm/dd/yyyy)

[aligahk06]

Dear All,

The attached excel sheet have date value in different cells , But excel won't recognise as date , So i want all cells date value recognise as a date value in (mm/dd/yyyy) format.

While applying formula on cell values it won't recognise as a date value.
Oct 1,2018, 2:05 PM Oct 1, 2018, 8:00 AM Oct 4, 2018, 5:00 PM
Oct 2, 2018, 12:26 PM Oct 2, 2018, 8:00 AM Oct 5, 2018, 5:00 PM
Oct 4, 2018, 9:27 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 9:28 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 9:29 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 9:31 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 10:15 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 7, 2018, 8:40 AM Oct 7, 2018, 8:00 AM Oct 9, 2018, 5:00 PM
Oct 8, 2018, 1:09 PM Oct 8, 2018, 8:10 AM Oct 9, 2018, 4:00 PM

Please assist,

Rgds,
aligahk06

 

[mole999]

regardless of the visual format all dates are integers, and time is less that one. how many columns across does the value occupy, is it 3 from Oct to PM ?

 

[Rick Rothstein]

Dear All,

The attached excel sheet have date value in different cells , But excel won't recognise as date , So i want all cells date value recognise as a date value in (mm/dd/yyyy) format.

While applying formula on cell values it won't recognise as a date value.
Oct 1,2018, 2:05 PM Oct 1, 2018, 8:00 AM Oct 4, 2018, 5:00 PM
Oct 2, 2018, 12:26 PM Oct 2, 2018, 8:00 AM Oct 5, 2018, 5:00 PM
Oct 4, 2018, 9:27 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 9:28 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 9:29 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 9:31 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 4, 2018, 10:15 AM Oct 1, 2018, 8:30 AM Oct 30, 2018, 5:00 PM
Oct 7, 2018, 8:40 AM Oct 7, 2018, 8:00 AM Oct 9, 2018, 5:00 PM
Oct 8, 2018, 1:09 PM Oct 8, 2018, 8:10 AM Oct 9, 2018, 4:00 PM

The problem is that second comma after the year. I also notice your first "date" does not have a space after the first comma. Here is a macro that will convert what you have into real date/time values which you can then format to display however you want. To use it, select all of the cells containing dates you want to fix, then run this macro...

[table="width: 500"]
[tr]
	[td]Sub FixDates()
  Selection = Evaluate("IF({1},SUBSTITUTE(SUBSTITUTE(" & Selection.Address & ","","","", ""),"","","""",2))")
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/showthread.php?63998-Excel-Won-t-recognise-as-date-(mm-dd-yyyy)

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.