String to date?

[eduzs]

IS there a formula to get the date from a string like that: "wedsneday, 14 of june of 2016"??
Thanks

 

[63falcondude]

Sure, but it won't be very pretty. Assuming that you have multiple text dates that you want to convert to excel recognized dates, are they all in that same format?

 

[ArnoldMS]

Hello try this formula:

 

[Rick Rothstein]

IS there a formula to get the date from a string like that: "wedsneday, 14 of june of 2016"??

Give this formula a try...

=0+MID(SUBSTITUTE(LOWER(A1),"of ",""),FIND(" ",A1)+1,99)

This formula returns the date's serial number so you will need to apply the Date Cell Format of your choice to the cell you put it in.

 

[ArnoldMS]

Hello try this formula:

Sorry wrong one, use this one instead:

 

[63falcondude]

Give this formula a try...

=0+MID(SUBSTITUTE(LOWER(A1),"of ",""),FIND(" ",A1)+1,99)

This formula returns the date's serial number so you will need to apply the Date Cell Format of your choice to the cell you put it in.

Impressive...

 

[godsaaint]

Give this formula a try...

=0+MID(SUBSTITUTE(LOWER(A1),"of ",""),FIND(" ",A1)+1,99)

This formula returns the date's serial number so you will need to apply the Date Cell Format of your choice to the cell you put it in.

I have tried your formula and another of mine, it works fine up until it has to sum the 0, basically, its doing "0+ 14 june 2016" which is returning #VALUE, any idea how to approach this?

 

[Rick Rothstein]

I have tried your formula and another of mine, it works fine up until it has to sum the 0, basically, its doing "0+ 14 june 2016" which is returning #VALUE, any idea how to approach this?

Are you saying the formula I posted is not working for you? Did you use it exactly like I posted it (except for changing the cell reference is your date string is not in cell A1)? Just so you know, the formula I posted works fine for me.

 

[godsaaint]

Correct. I'm trying exactly the one you posted, same reference, "wedsneday, 14 of june of 2016"(no quotes) in A1, and it's returning #Value!

 

[Rick Rothstein]

Correct. I'm trying exactly the one you posted, same reference, "wedsneday, 14 of june of 2016"(no quotes) in A1, and it's returning #Value!

Maybe your spaces are not actually "spaces" (ASCII 32)... if you got your data from the web, there is a good chance they are non-breaking spaces (ASCII 160). See if this version of my formula works for you...

=0+MID(SUBSTITUTE(LOWER(SUBSTITUTE(A1,CHAR(160)," ")),"of ",""),FIND(" ",SUBSTITUTE(A1,CHAR(160)," "))+1,99)