Date Conversion Question

[jekl1000]

Hi all,

I have a data export that provides the date as MMM DD, YYYY. Because of this format it is not being recognized as a date that I can convert to MM/DD. Does anyone know of a way to convert something like Aug, 15, 2017 to 8/15? Any help would be greatly appreciated.

Kyle

 

[wideboydixon]

Something like this? (Depends on whether there's a comma after the month):

Excel 2016 (Windows) 32 bit
	A	B
1	Aug, 15, 2017	08/15
2	Aug 15, 2017	08/15
Sheet2

Cell Formulas
Range	Formula
B1	=DATEVALUE(MID(A1,6,2)&"-"&MID(A1,1,3)&"-"&MID(A1,10,4))
B2	=DATEVALUE(MID(A2,5,2)&"-"&MID(A2,1,3)&"-"&MID(A2,9,4))

WBD

 

[Joe4]

Is there a comma after the month or not?
You say the format is:
MMM DD, YYYY

but you show an extra comma in your example:
Aug, 15, 2017

If it does not have that extra comma, Excel has no problem converting this to a date.
My guess is that the data is being read in as Text instead of a Date.
You can easily convert a column of Text entries like this to a Date using "Text to Columns" from the data menu.
Or, you can use the DATEVALUE function to convert it to a valid date, where you can choose any Date Format you like.

If you need it to be the Text value of "MM/DD" (and not really a date), this formula would do that:

=TEXT(DATEVALUE(A1),"mm/dd")

 

[jekl1000]

Wow you guys are fast! So sorry, that was a typo in the example. The data is provided as MMM DD, YYYY

 

[jekl1000]

I tried the DATEVALUE function and it worked perfectly. Thank you so much for your help!