Hey everyone, I’m struggling with date formatting in my spreadsheet. I want to display dates in different languages without changing my account settings. Right now, I use this formula:
=TEXT("01/01/2020","DD MMMM YYYY")
It shows “01 January 2020” in English. But I need it to show “01 Enero 2020” in Spanish, even if my spreadsheet is set to English.
I tried looking for a function like:
=TRANSLATEDATE("ES", "01/01/2020", "DD MMMM YYYY")
But I can’t find anything that works. Does anyone know how to do this? It would be super helpful for my project. Thanks in advance for any tips!
I encountered a similar challenge in a project I worked on. One solution that proved effective was using the GOOGLETRANSLATE function, if you’re using Google Sheets. The formula would look something like this:
=DAY(A1)&" “&GOOGLETRANSLATE(TEXT(A1,“MMMM”),“en”,“es”)&” "&YEAR(A1)
This assumes your date is in cell A1. The function translates the month name from English to Spanish. For Excel, you’d need to set up a custom VBA function or use Power Query to achieve similar results.
Remember, this method requires an internet connection to work. It’s not perfect, but it’s a straightforward way to handle multi-language date formatting without changing system settings or creating extensive lookup tables.
hey there, i’ve run into this too. have u tried using the SWITCH function? it’s not perfect but it works ok. u can do something like:
=DAY(A1)&" "&SWITCH(MONTH(A1),1,"Enero",2,"Febrero",…,12,"Diciembre")&" "&YEAR(A1)
just fill in all the months. it’s a bit clunky but gets the job done without changing settings
I’ve dealt with a similar issue in my work, and I found a workaround that might help you. Instead of using a built-in function, you can create a custom solution using VLOOKUP or INDEX/MATCH with a reference table.
First, create a sheet with month names in different languages. Then, use a formula to extract the month number from your date, look up the corresponding name in your reference table, and reconstruct the date string.
It’s a bit more work upfront, but it’s flexible and works across languages. You’ll need something like:
=DAY(A1)&" “&VLOOKUP(MONTH(A1),MonthTable,2,FALSE)&” "&YEAR(A1)
where MonthTable is your reference range with month numbers and names in various languages.
It’s not as elegant as a single function, but it gets the job done without changing system settings. Hope this helps!