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!
 
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?
This will fix your issue if you want to use the UDF. Format the formula cells as "mm/dd/yy".

Code:
Function ConvertToDate(S As String)
Dim V As Variant, M As Variant, D As Variant, Y As Variant
V = Split(Application.Trim(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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
One more

=--SUBSTITUTE(LEFT(A1,FIND(":",A1)-4)," ",", ")


Needs the TRIM in order to handle the two spaces in front of the single digit Day.

=--SUBSTITUTE(LEFT(trim(A1), FIND(":", trim(A1)) -4)," ",", ")

YES - I am behind, just skimming Excel questions and working through them. Better then watching the boob tube...
 
Upvote 0
Now this is really old, but it's still on the MrExcel Home page, and it intrigued me.

Here's my version:
=--LEFT(SUBSTITUTE(SUBSTITUTE(A1, " ", ", "), ",", " ",3), 20)
 
Upvote 0
Now this is really old, but it's still on the MrExcel Home page, and it intrigued me.

Here's my version:
=--LEFT(SUBSTITUTE(SUBSTITUTE(A1, " ", ", "), ",", " ",3), 20)

Sorry, that should be:

=--LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", " ")," ", ", "), ",", " ",3), 20)
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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