Reverse engineer foreign date format (or other solution)

[Anon9149]

Hi all,

With respect to the attached example.

Is there any way of taking a date that has already been formatted into a non-English language (e.g. Spanish) and applying a formula to convert it into another language e.g. back to English, Portuguese, German etc.?

For context, I have built a financial statements report that I've put together for monthly financial reporting purposes for our overseas entities. One of the features of the report is that it is multi-lingual, so it can be utilised by our non-English speaking regions. The document has been built such that the foundation language is English, whilst the foreign language feature runs on an INDEX, MATCH function from a 'Master Languages' worksheet that holds the foreign translations for all words in the document. Users can select another language from a DV list, the INDEX,MATCH runs and converts all words and phrases in the document into the foreign language.

I have almost finished the document, but one final problem stands in my way. For reasons too convoluted to go into here, I need to be able to convert a date (that has already been translated into a foreign language) into another language. I draw your attention to cell D11 of the attached, which attempts to take a foreign date (D7) and the English date format and convert back to English, but doesn't. I suspect this is because my Excel settings are English, so Excel is only capable of taking English dates and converting them into a foreign format, and not the other way around. So is there a way of 'reverse engineering' a foreign date into English, so then it can be converted into another foreign language? Or any solution?

Thanks in advance!

 

[bosco_yip]

Try,

Here is the formula I used.

In [E7]: =PROPER(TEXT($D$3,INDEX($G$4:$K$4,MATCH($D5,$G$3:$K$3,0))&"dd mmmm yyy"))

In [E11]: =PROPER(TEXT($D$3,INDEX($G$4:$K$4,MATCH($D9,$G$3:$K$3,0))&"dd mmmm yyy"))

 

[Anon9149]

Here is the formula I used.

In [E7]: =PROPER(TEXT($D$3,INDEX($G$4:$K$4,MATCH($D5,$G$3:$K$3,0))&"dd mmmm yyy"))

In [E11]: =PROPER(TEXT($D$3,INDEX($G$4:$K$4,MATCH($D9,$G$3:$K$3,0))&"dd mmmm yyy"))

Thanks Bosco. Unfortunately - and again, for reasons too convoluted to go into here - I cannot use D3 as a reference in the formula titled 'Convert Back'. In a nutshell, the DV list which triggers the INDEX MATCH completes 95% of the document language conversion. The other 5% needs to be done by macro (which I've set up) but once the macro has run, D3 no longer has data in it, only D7 remains. So I need to be able to use the data in D7 to either (1) convert directly to another foreign language or (2) revert back to English, which will then allow me to convert to another foreign language

 

[Peter Bartholomew]

I would suggest not translating dates since they are already language independent (today being day 44864).
Conditional formatting can be used to display a date in the selected format without undermining the numerical date value and preventing further calculation.

 

[Anon9149]

I would suggest not translating dates since they are already language independent (today being day 44864).
Conditional formatting can be used to display a date in the selected format without undermining the numerical date value and preventing further calculation.

Thanks Peter. I do have a follow-up question, if you would oblige me? How does the 'Foreign Date' (cell D7) conditional format to Portuguese? All I can see at present is that cell D7 references 'InputDate' (cell D3 as per the Name Manager) but D3 is English. I see that you have made reference to Conditional Formatting, but when I open the Conditional Format dialogue box in cell D7, the only formatting I see is 'dd mmmm yyyy'. Why isn't D7 just showing the English 'dd mmmm yy'? How is Excel converting this to Portuguese? I suppose the same question applies to the reversion back (D9).

Thanks for your help, you have provided the solution I need, I just need assistance understanding the solution so I can configure it in my actual document.

 

[Peter Bartholomew]

Hi. Although it may not look it D3, D7, D9 and D11 contain precisely the same value with the formula daisy chained. What differs is the condition formatting which is based upon a formula referencing cell D5.




Without conditional formatting, the display will normally default to conform to the language setting of the computer.