Date format independent from regional settings

[Assamita]

Hi guys,
I have a file that is shared with several people. Some of them have Spanish regional settings, and some of them English (I don't think there's anything else than those two).
The thing is that the file has a date column, and I have a format "mm - aaaa" which for those who have it in spanish, displays as "03 - 2019", but for those who have it in english, displays as "03 - aaaa". They would require to have it as "mm - yyyy", but then the ones with Spanish wouldn't see it properly.

Is there a way to format a date, independently of the regional settings of the viewer?

Thank you

 

[James006]

Hi,

There are several possibilities ... for example:

yourvariable[COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] VBA[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Format[/FONT][/COLOR][COLOR=#303336][FONT=inherit]$([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] strFormat[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR]

and define strFormat as your ' constant ' format ...

Hope this will help

 

[Assamita]

Can't it be done without VBA?

 

[James006]

Yes ... it can be done with a Formula ...

=TEXT(TODAY(),"[$-809]mmmm yyyy")

 

[Assamita]

Well, that's actually what I have:

=TEXT([@Date];"[$-409]aaaa - mm")

but I need to use aaaa - mm because my regional setting is for Spain. And if someone else with a different regional setting opens the file, all they see is "aaaa - 03", instead of "2019 - 03" like I do. It has to do with each person's regional configuration.

 

[James006]

409 is for English (U.S.) and 0C0A is for Spanish ...