Dateformat must be the same for all users even on different locale settings

New Contributor

Hi Techcommunity ! 

 

I am struggling to "lock" date format in only one format regardless dateformat source (for both Excel Web and App)  --> due to Windows local settings it might be difficult but I'm sure that one of your brilliant mind might find a formula, VBA or M code in Query to do the work

 

There is indeed posts about solutions for an Excel Web file already unless that Users must open the file in Excel App to be able to refresh query to an external salesforce report (not supported by Excel Web yet) without modifying/transposing the dateformat to their windows/excel/brower local/regional settings.
Ps : I am particularly struggling with russian format which is : dd.mm.yyyy

some have dot, hyphen or slash... 


To make it short : 
enter date :                                              Outcome :
yyyy/mm/dd                                            mm/dd/yyyy (for all)

dd/mm/yyyy

yyyy-mm-dd

dd.mm.yyyy

and so one.

 

I thought about an exel listeof datesformat that the app knows already and it has been coded that way and combine it with a kind or tanspose/transformat into this outcome : mm/dd/yyyy

 

Thanks a lot in advance for you help !

 

4 Replies

@BeryLine 

Why not use the ISO 8601 standard for international use; at least each machine can understand the format regardless of locale.  I also note that you want all your users to adopt the US standard which is the least understood (and the most illogical) of the candidate formats.

Date format by country - Wikipedia

The danger of allowing multiple input formats is that the dates will be accepted as dates, but not the date intended.

@gvaultak1770 

It is an international standard for representing dates.
https://en.wikipedia.org/wiki/ISO_8601

In essence, it is the format "yyyy-mm-dd".

It is logical, unambiguous but distinctly unfamiliar to most.

By way of example, I know, without shadow of doubt, that 7/4/22 is the 7th April, whereas I would see 2022-07-04 that way, even if it needed a double take to read it at all.

Hi
Thanks for a lot for this reply.

One point I did not precise : dateformat outcome must not only be the us one.
It could be anything else as long as I chose only one dateformat as outcome.
Lets pick up the iso one.
It worked indeed, I tried it last week but was not working for all date format especially with the Russian one...
Could not transform the dd.mm.yyyy into the iso one.
Any idea how to proceed?
Thanks.