Text Dates....

[Still_Learning_Excel]

I have a column of data that includes date/time in a text format (Dec 28, 2016 10:32 AM). What I would like to do is use a formula to return just the date element of the field (i.e. 28/12/2016). I've tried using the INT and DateValue functions, but I just get a #Value ! error.

All help appreciated.

 

[FormR]

Hi, here is one option you can try:

Excel 2013/2016
	A	B
1	Dec 28, 2016 10:32 AM	28/12/2016
2	Dec 1, 2016 10:32 AM	01/12/2016
Sheet1

Cell Formulas
Range	Formula
B1	=0+(MID(A1,5,FIND(",",A1)-5)&LEFT(A1,3)&MID(A1,FIND(",",A1)+2,4))

 

[Rick Rothstein]

I have a column of data that includes date/time in a text format (Dec 28, 2016 10:32 AM). What I would like to do is use a formula to return just the date element of the field (i.e. 28/12/2016). I've tried using the INT and DateValue functions, but I just get a #Value ! error.

If you "date" cell is formatted as Text, then give this formula a try...

=INT(0+A1)

This will return the serial date value so you will have to use Cell Formatting to make it display the way you want.

 

[FormR]

I've tried using the INT and DateValue functions, but I just get a #VALUE ! error.

The #VALUE issue is probably because UK/EU regional settings require the day before month for coercion to work.

Excel 2013/2016
	A	B
1	Dec 28, 2016 10:32 AM	#VALUE!
2	28 Dec, 2016 10:32 AM	28/12/2016 10:32
Sheet1

Cell Formulas
Range	Formula
B1	=0+A1
B2	=0+A2