I’m working on a Google Sheets project where I need to show dates in multiple languages without having to change the entire spreadsheet language settings each time.
Currently when I use =TEXT("12/25/2023","DD MMMM YYYY") with English settings, it shows 25 December 2023. When I switch my Google Sheets language to French, the same formula gives me 25 Décembre 2023.
What I really want is a formula that can convert dates to any language programmatically. Something like =CONVERTDATE('FR', TEXT("12/25/2023","DD MMMM YYYY")) that would return Décembre even when my spreadsheet is set to English.
Is there a built-in function or workaround to achieve this kind of date translation functionality in Google Sheets?
I ran into this exact problem with international clients. Google Sheets doesn’t handle multilingual dates natively, but I found a solid workaround using VLOOKUP tables for month translations. Here’s what works: Create separate sheets with month names in different languages. Then use MONTH() to pull the numeric month and VLOOKUP to grab the translated name. You’d combine DAY(), VLOOKUP(MONTH(date), translation_table, 2, FALSE), and YEAR() with concatenation. This skips the Apps Script headache and handles basic date formats well. You can throw in day-of-week translations too if needed. Takes some setup upfront, but you get full control over formatting without messing with locale settings.
hmm, not really a built-in way for that. you’d have to dive into Apps Script and whip up a custom function. try using Intl.DateTimeFormat in JS for different langs. it seems a bit of a hassle at first, but trust me, it’s worth it!
I hit this exact problem last year building a multilingual dashboard. Google Sheets doesn’t natively support language-specific date formatting without switching locale settings. Your best bet is a custom function through Google Apps Script using JavaScript’s Intl.DateTimeFormat object. Write something like function formatDateInLanguage(date, locale) then call it with =formatDateInLanguage(A1, "fr-FR"). The function handles conversion internally while your spreadsheet locale stays unchanged. You could also maintain translation tables for month names and use SUBSTITUTE functions, but that gets messy with multiple languages.