Convert Text to Date

[thedeadzeds]

Hi Guys,

I have the following text in a cell which I would like to convert to a date format dd/mm/yyyy

01/Oct/2016

Is this possible?

Thanks
Craig

 

[steve the fish]

Try this first:

=DATEVALUE(A1)

 

[thedeadzeds]

thanks steve this shows an error as per below

#VALUE!

<tbody>
</tbody>

 

[Rick Rothstein]

thanks steve this shows an error as per below

#VALUE!

<tbody>
</tbody>

Steve's formula worked for me. Do you have your date in cell A1 (which is where Steve's formula is looking for it)?

Here is another formula you can try...

=0+SUBSTITUTE(A1,"/","-")

 

[thedeadzeds]

thanks both, I've noticed that there is a space at the start of the cell which is why this doesn't work. As soon as I delete the space at the start its fine. Is there any way to accommodate for the space?

 

[Rick Rothstein]

thanks both, I've noticed that there is a space at the start of the cell which is why this doesn't work. As soon as I delete the space at the start its fine. Is there any way to accommodate for the space?

Use the TRIM function...

=DATEVALUE(TRIM(A1))

or

=0+SUBSTITUTE(TRIM(A1),"/","-")

 

[Dave Patton]

To convert in place

Copy a blank cell Ctrl C
then With Paste Special use Add

Or to put the Date in another cell and assuming Text is in A1 =A1+0

If this does not work, check for extraneous characters before or after the text.

 

[thedeadzeds]

thanks rick, i still get the value error but if I delete the space as per before it works fine

 

[Rick Rothstein]

thanks rick, i still get the value error but if I delete the space as per before it works fine

Did you get your values from a web page? If so, that is probably not a normal space (ASCII 32), rather, it is probably a non-breaking space (ASCII 160) instead. See if these work for you...

=DATEVALUE(TRIM(SUBSTITUTE(A1,CHAR(160),"")))

or

=0+SUBSTITUTE(TRIM(SUBSTITUTE(A1,CHAR(160),"")),"/","-")

 

[thedeadzeds]

yes they are from a webpage, this works perfectly thank you very much