Not sure if this is a formula question, a Macro question or an Excel question.
I have a macro that I wrote that will not work when I share it with a co-worker who's excel is in Spanish. The line of code takes the date that is written "W43-2018\22th October" and translates it to "10/22/2018". The Macro code is :
ActiveCell.FormulaR1C1 = "=DATE((MID(R[-1]C,5,4)),(MONTH((DATEVALUE(MID(R[-1]C,FIND(""th"",R[-1]C,1)+3,LEN(R[-1]C)-(FIND(""th"",R[-1]C,1)+3-1)))))),(MID(R[-1]C,10,(FIND(""th"",R[-1]C,1)-1-10)+1)))"

which enters this formula:
"=DATE((MID(D1,5,4)),(MONTH((DATEVALUE(MID(D1,FIND("th",D1,1)+3,LEN(D1)-(FIND("th",D1,1)+3-1)))))),(MID(D1,10,(FIND("th",D1,1)-1-10)+1)))"

but when the middle part "MONTH((DATEVALUE(MID(D1,FIND("th",D1,1)+3,LEN(D1)-(FIND("th",D1,1)+3-1))&1)))" returns October, the Spanish user gets an error. This works fine for all English users. We tried switching her language to English to run it and we got the same issues. any idea how to fix this? We cannot change the way the date comes in (W43-2018\22th October) either.