HELP!!!! How to change text datetime to date format

[TheBoss_U2]

Hello all,

I'm trying to change a text datetime "13 de diciembre de 2013 10:27:37 AM GMT-05:00", to date format

I used

DATEVALUE(LEFT(A1,23)), and always receive #VALUE!

DATE(MID(A1,1,2),MID(A1,4,12),...) but when I put the month value, it returns #VALUE!

 

[NARAYANK991]

Hi ,

You can use the DATE and the DATEVALUE functions only if the parameters you pass to them are compatible with your system date format ; thus , if your system date format is mm/dd/yyyy , if you try to use the DATEVALUE function as =DATEVALUE("dd-mm-yyyy") , it will not work.

Can you say what your system date format is like ?

Secondly , if your regional setting is English , a month name such as diciembre will not be recognized as December.

Narayan

 

[TheBoss_U2]

My system date format is dd/mm/yyyy and my regional setting is Spanish; hope someone can help me to solve this

 

[NARAYANK991]

Hi ,

Try this :

=DATEVALUE(LEFT(text;2) & "/" & MID(text;7;9) & "/" & MID(text;20;4))

where text stands for your text input viz. 13 de diciembre de 2013 10:27:37 AM GMT-05:00

Narayan