Text to Date Conversion

[Richard Green]

This is probably an age old question, so apologies in advance...

I'm trying to import data into Excel from a csv file that contains a date
field of the format "Mon 01 Jun". There seems to be no easy way to get
Excel to recognise this as a date, unless anyone can tell me differently.

Assuming there isn't a quick import / text-to-columns conversion, what is
the easiest way with a function to create a new cell that contains a date
from this text?

Thanks,
Richard.

 

[Bernard Liengme]

With the text in A1; assuming all months are 3 characters
=DATE(2009,MATCH(RIGHT(A1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),--MID(A1,5,2))
best wishes

 

[Ragdyer]

Try this in a cell formatted to Date:

=--TEXT(RIGHT(A1,6),"m/d/yy")

 

[Richard Green]

Thanks, that does the trick, as did Bernard's solution. My solution, after
I'd stared at it for a while was:
=DATEVALUE(CONCATENATE(RIGHT(A2,3),"-09"))
with the cell formatted to display in a date format... functional but a bit
of a kludge, and not really resulting in a date value.

Yours appears to be the neatest solution, don't think I've used the text
function before, looks like it will be very handy in the future.

Regards,
Richard.

 

[Ragdyer]

You're welcome, and thanks for the feed-back.

 

[Shane Devenshire]

Hi,

Here's another solution

=--MID(A1,5,6)

Format as desired.

Fair warning for all of these, the assumption is that the year is the
current year.