Converting text to date

[pmich]

etaf
In my file I have many rows in Col A with date and Time format. I want to convert to date in Col C.
In my file [Nov 2, 2019 8:10:20 AM] was in A2.
I used [=MID(A2,1,12)] in B2 and got [Nov 2, 2019] in B2.
As per your suggestion I used [=VALUE(RIGHT(B2, 16))[ and got [43776] in C2.
I did format C2 as [DATE] and chose [14-Mar-2001] [Eng (US)] and got [2-Nov-2019].
A. Am I doing right or should I do something else to make it easier?
B. Kindly suggest me how to use in vba.

 

[etaf]

instead of using 3 coloumns
=value(MID(A2,1,12))
and format
i dont know vba

 

[pmich]

etaf
Thanks for the simplified formula.
It works.

 

[etaf]

you are welcome

 

[lrobbo314]

If the data you have in column A are actual dates formatted as text, i.e. the underlying value is a date value and not text, then you can use the Int formula to get rid of the time portion of the date. Date/Time values are really just numbers. The date portion is the whole number and the time is represented by the decimal portion.

Book1
	A	B
1	November, 2 2019 8:10 AM	11/2/2019
Sheet2

Cell Formulas
Range	Formula
B1	B1	=INT(A1)

 

[alansidman]

an alternative solution is to use Power Query/Get and Transform. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type date}})
in
    #"Changed Type1"

 

[lrobbo314]

If it is purely text, Power Query can convert it pretty easily too.

Book1
	A	B	C
1	Raw		Raw
2	Nov 2, 2019 8:10:20 AM		11/2/2019
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    textToDateTime = Table.TransformColumnTypes(Source,{{"Raw", type datetime}}),
    dateTimeToDate = Table.TransformColumnTypes(textToDateTime,{{"Raw", type date}})
in
    dateTimeToDate

 

[pmich]

lrobbo314
INT works.
Thanks.
alansidman
I am still trying some examples in LET, GET. So, I will try Power Query later. thanks for your suggestion.