• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Convert Date Text to Regular Date Format

Hello:

I have a spreasheet that has text dates in Column A. I want to convert the dates so they look like this 11/13/2009, 12/07/2009, etc...

NOV 13 2009
DEC  7,2009
NOV 24,2009
DEC 14,2009
OCT  8,2009
OCT 21,2009
NOV  6,2009

I tried using this formula in Column B
 =DATEVALUE(MID(A1,4,2)&"/"&MID(A1,1,3)&"/"&MID(A1,7,4))
but I get the #Value Error.

Dan
0
RecipeDan
Asked:
RecipeDan
  • 3
  • 2
2 Solutions
 
jppintoCommented:
You get the error because at the end of your formula you need to change the 4 by a 5, like this:

=DATEVALUE(MID(A1,4,2)&"/"&MID(A1,1,3)&"/"&MID(A1,7,5))

jppinto
0
 
jppintoCommented:
But your formula doesn't work for the rest of the rows...just for row 1.
0
 
barry houdiniCommented:
I think you have some double spaces so that throws the formula out, try this version

=DATEVALUE(MID(A1,5,2)&"/"&LEFT(A1,3)&"/"&RIGHT(A1,4))

regards, barry
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
barry houdiniCommented:
I expect my suggestion above to work, but when I copied your dates from the board some of the characters are CHAR(160)s rather than spaces. I assume that's just Experts Exchange converting the data but if those are in your original data you might need this version

=DATEVALUE(SUBSTITUTE(LEFT(RIGHT(A1,7),2),CHAR(160),"")&"/"&LEFT(A1,3)&"/"&RIGHT(A1,4))

regards, barry
0
 
jppintoCommented:
I tryed with this:

=DATEVALUE(MID(A1,FIND(" ",A1,1),3) & "/" & LEFT(A1,3) &"/" & RIGHT(A1,4))

It works on most of the values you presented. Are the double spaces for real or are they just a typo?
0
 
RecipeDanAuthor Commented:
Thanks both of you for your help. The double spaces are real. It was a spreadsheet that was given to me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now