Problem with language

hello

I am trying to replace months with their respective number using

=MONTH(1&A1)

(columm A contains the month name)

The problem is: if I use it in english like january, february and so on, it works fine. However, my excel, windows system and data is in Portuguese: so I have Janeiro, Fevereiro and etc.
If the month is in portuguese, it returns a #value error.
I find it weird because the code works fine in my coworker's excel who sits at my side while using portuguese months name. Anybody know how to fix this issue?

Best regards.

(I put this question here because I think it's not function related. If it should be in the function related forum, I'm sorry)

Originally Posted by ben_hensel

Honestly rather than mucking about with language settings, I'd just brute-force it with a lookup function:

=MATCH(LOWER(A1), {"janeiro","fevereiro","março","abril","maio","junho","julho","agosto","setembro","outubro","novembro","dezembro"}, 0)

Or maybe just match the first three letters:

=MATCH(LOWER(LEFT(A1, 3)), {"jan","fev","mar","abr","mai","jun","jul","ago","set","out","nov","dez"}, 0)

It's pretty brute force, but I feel like it's a more transparent approach that will be more independent of language settings, and also more apparent to debug if something goes wrong in the future.

You could even get really slick and nest with IFERROR, or build a SUMPRODUCT, that will assess for both English and Portuguese.

I hate brute forcing stuff =P but that seems a fast solution to use for now. Thank you sir. If I ever find out somehow the solution I will post here

Originally Posted by FDibbins

Do you have the PC regional settings set up for your location? (compare with the other PC)

Yep, both are set to Brasil