Converting date to different format.

JokerFMJ

Board Regular
Joined
Mar 7, 2003
Messages
110
Hey, all.

I receive a spreadsheet each day with up to 1000 rows. In two of these columns I am provided a date in a text formatted column with the following format:

Code:
May 31 2007 12:00AM

However, I need to put the date in MM/DD/YY format. I have tried my usual splitting, concatenate, etc... but I think due in part of it being a text based column in the first place, it is just now working. I am having to split them all, concatenate them, and then manually replace "May" with "5" and do that for each month. Obviously this is tedious and time consuming.

Does anyone have any suggestions on how I can get to formatting this in a way that I could add into a macro?

Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I get the #VALUE! error when I input that formula. When I remove the "+0" from the end it returns the date in, "May 312007" format. If I add a space in the second set of quotes then I am given, "May 31 2007". Could I not do this through =left(A1,11)?

I'm still trying to get it into MM/DD/YY format.
 
Upvote 0
Here's a UDF you can try. Cell with formula in it is formatted as mm/dd/yy.
Excel Workbook
AB
2May 31 2007 12:00AM05/31/07
Sheet6


Code:
Function ConvertToDate(S As String)
Dim V As Variant, M As Variant, D As Variant, Y As Variant
V = Split(S, " ")
M = V(0)
For i = 1 To 12
    If MonthName(i) = M Then
        M = i
        Exit For
    End If
Next i
D = V(1)
Y = V(2)
ConvertToDate = CDate(M & "/" & D & "/" & Y)
End Function
 
Upvote 0
Dear


In addition to previous equation by JonMol =SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)+0

you can make it as follow:



Code:
=MONTH(TEXT(LEFT((SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)+0),3),"m"))

Hey, all.

I receive a spreadsheet each day with up to 1000 rows. In two of these columns I am provided a date in a text formatted column with the following format:

Code:
May 31 2007 12:00AM

However, I need to put the date in MM/DD/YY format. I have tried my usual splitting, concatenate, etc... but I think due in part of it being a text based column in the first place, it is just now working. I am having to split them all, concatenate them, and then manually replace "May" with "5" and do that for each month. Obviously this is tedious and time consuming.

Does anyone have any suggestions on how I can get to formatting this in a way that I could add into a macro?

Thanks!
 
Upvote 0
Make sure you got the formula copied exactly, it works for me..


Excel 2010
ABC
1May 31 2007 12:00AM5/31/2007May 31, 2007
Sheet1
Cell Formulas
RangeFormula
B1=SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)+0
C1=SUBSTITUTE(LEFT(A1,LEN(A1)-8)," ",", ",2)
 
Upvote 0
another way

=MID(A13,FIND(" ",A13),3)&"/"&MONTH((LEFT(A13,FIND(" ",A13)-1)&" 0"))&"/"&MID(A13,FIND("@",SUBSTITUTE(A13," ","@",2)&"@")+1,5)

OR

=MONTH((LEFT(A13,FIND(" ",A13)-1)&" 0"))&"/"&MID(A13,FIND(" ",A13)+1,2)&"/"&MID(A13,FIND("@",SUBSTITUTE(A13," ","@",2)&"@")+1,5)
 
Last edited:
Upvote 0
Joemo and Jonmo, both of your suggestions work great with one exception. When the day is a single digit (for example, August 8th), it comes across as:

Code:
AUG  8 2013 12:00AM

This is causing the both suggestions to return a #VALUE error. Is there a way to incorporate a fix in this or will I just have to replace all double spaces with single spaces?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top