Text to Date Conversion

  • Thread starter Thread starter Richard Green
  • Start date Start date
R

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.
 
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
 
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.
 
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.
 
Back
Top