Converting dates in text format to date format.

[PJF]

I can do this manually using =Datevalue("02/14/2007"). But all my dates
(several hundred of them) are in the text form (02/14/2007). I've tried
writing a macro to add the quotes at either end of the text form date within
the parens but I can't get the macro to add the quotes without copying the
first date in the column. I should be able to figure this out but so far all
my efforts have been futile. Any suggestions would be appreciated.

Thanks and regards,

PJF

 

[Gord Dibben]

Try Data>Text to Columns>Next>Next>Column Data Format>Date>MDY>Finish.

Assuming you don't really have the parens () per your example.

If so, clean them up first with Edit>Replace.


Gord Dibben MS Excel MVP

 

[Guest]

To get the text try:

= """" & mid(a1,2,8) & """"

 

[Guest]

Slight error should be 10 and not 8 in

= """" & mid(a1,2,8) & """"

ie

= """" & mid(a1,2,10) & """"

 

[PJF]

Martin,

Thanks for your feed back. Your formula: = """" & mid(a1,2,10) & """",
works fine to put the text date in the proper format to use with the
function: =datevalue("text date"), except that I had to change the "2" in
the formula to a "1".

However, the solution to my problem still eludes me. For example:

cell A1 has a text date of 01/01/2004. I apply your formula and get
"01/01/2004" in cell B1. So far so good. Theoretically I should then be
able to use the =datevalue function referencing B1, i.e. =datevalue(b1), and
get a numerical result (in this case 37987). But, instead, I get a #VALUE!
error message.

While I was skeptical as to the result, I also tried to use the formula:
"""" & mid(a1,2,10) & """", with datevalue, i.e., =datevalue("""" &
mid(a1,2,10) & """"), but that errors out as well with #VALUE!.

I regret imposing further on you. But, if you have any additional insights
or suggestions, I'd be most grateful.

PJF

 

[PJF]

Gord,

Worked perfectly! I'm most grateful.

PJF

 

[Gord Dibben]

Thanks for the feedback.

Gord

Gord,

Worked perfectly! I'm most grateful.

PJF

 

[Guest]

It appears that Gord's solution works best.

Just out of interest what is actually in the cells because you said that
tthe format was: '(02/14/2007)' wit no quotes and that you wanted it to be
enclosed in quotes.

So what you are saying that actually in the cell was '02/14/2007' without
the quotes.

you could try this one:

=date(right(a1,4), left(a1,2), mid(a1,4,2))

and that should cut the date up.

 

[PJF]

Martin,

What was actually in the cell was a date in text format, i.e., 01/14/2007,
no quotes, parens or other delimiters than the slash marks between MD&Y.

Gord's solution worked perfectly. I will, however, give your suggestion a
try and keep handy should your approach prove more efficient in a given
situation.

Many thanks and kindest regards,

PJF