Hi everybody,
I'm always busy trying out new ideas and implementing ideas and whatever comes up in my mind and see if I can do it in Excel.
I know how to find all the information using xlInternational and all that.
The topic on myonlinetraininghub how to display dates in their own language gave me some ideas and what I have not been able to find yet (or didn't look correctly) is the following: For UK English I know the language ID 0809 or language TAG en-GB
Using one of these I would like to find the corresponding longdate numberformat like if I use it in the TEXT() function:
TEXT(Date,[$-809] dddd, dd mmmm yyyy") but if I do not know how the numberformat is and have hardcoded it
for example in Spain the say Dayname, day 'de' monthname 'de' year TEXT(Date,"[$-40A] dddd, dd 'de' mmmm 'de' yyyy")
Is there's a way the looking up the Language ID (or TAG) I can find the numberformat for that particular language?
How I've been able to explain myself.
Thanks for taking a look and any tips and ideas are welcome
BTW: I'm using Excel 2021 but would like it to work with older versions too
Hello,
Not a fully correct result, but perhaps it can be adjusted further. I am not good with VBA so I haven't checked for such solution.
Asking around in some ai chats I got the hint to use the TEXTJOIN function, as it would adjust how the long date is presented based on the language tag. Attached is a modified copy of Myndas example file. What is missing is the dividers and those differs (of course) from country to country.
In essential, the formula is as following:
=TEXTJOIN(" ",TRUE,
TEXT([@Date],[@[Language Tag]]&"DDDD"),
TEXT([@Date],[@[Language Tag]]&"D"),
TEXT([@Date],[@[Language Tag]]&"MMMM"),
TEXT([@Date],[@[Language Tag]]&"YYYY"))
Br,
Anders
Thank you Anders,
I don’t think this will do the trick I need but… I’ll try it later and let you know
Hi Anders,
Well, the information you attached was/is known to me but that doesn't achieve what I really want
What I am looking for is that let's say I create a file on my system and have the systems date settings set to dd/mm/yyyy hh:mm:ss I automatically get (what i can do now using VBA) Application.International(xlDateOrder)
The code I use now I found on Tek-Tips-Forums and VBAExpress which was posted by Tyny Jollans. I adapted it to my needs and my UDF FORMATDATE returns either is 0 - MM/DD/YYYY, 1 - DD/MM/YYYY and 2- YYYY/MM/DD as text and this I use in my other VBA to display the current date in the system's date format
A3=Format(myDayte, FORMATDATE)
There is more for the time settings too not used here
So I think I'll have to stick to VBA if I cannot get the Locale date and time settings in a non VBA Function.
Hope my explanation makes sense.
Thanks again for looking and answering
To explain it better, if you look at the Table of the attached (Mynda's file) you can see how it's done
What I need is that when the file is opened (no macros) it finds the user's system current language tag and then lookup and show long date format is the actual way it's used, In Span the date format is also dd/mm/yyyy but when shown in as text format it shows as Domingo, 8 de septiembre de 2024 with the two words 'de' but if you create this in VBA the 'de' will be displayed as 18 and the e disappears since is non-valid and shows as Domingo, 8 18 septiembre 182024
In Frech the same so probably all that will have to done by splitting the number format and using concatenate and all that.
Well, it's fun and in VBA it can be solved with some extra code bu non-vba solutions are more complicated (I think)
Hello,
Perhaps a Power Query solution is something that would work.
Added a custom column using Date.ToText.
= Table.AddColumn(#"Omsorterade kolumner", "Anpassad", each Date.ToText([Date],"D",[Language Tag]))
Br,
Anders
Hello Anders,
Thank you again. I think I've got sufficient ideas to play around with.
I'm marking your lats post as Useful Answer.
Cheers,
Hans
"IT" Always (keeps) crossing your path ... 
