"Delocalized" date format

[zio69]

I would like to manipulate dates in an excel worksheet (that is avoiding VBA,
I guess i could accomplish my goal with VBA) in a way that would make my
worksheet work in every excel localization. Unfortunately I need to use
formatting such as TEXT(C1, "ddd") (short day name) which works fine in
english.... but will produce a cell containing ddd in french, italian, german
and possibly many other locales. Unlike VBA, excel does not translate this
kind of strings....
Similarly, when I use DATEVALUE the date needs to be in mm-dd-yyyy format in
US and UK, while it's dd-mm-yyyy in other countries; I guess VBA can take
care of that too, but....

Is there a "universal" date format that is recognized by every localization
and can therefore be used in every date-related function??

Thanx for your input....

 

[Bernard Liengme]

The ISO date format is yyyy/mm/dd
It makes more sense than others in that it goes from large to small like 3
yards 2 feet 5 inches
best wishes

 

[zio69]

Yes, that would be nice.... but unfortunately it still uses "localized"
string yyyy/mm/dd. So, if I want the worksheet to work correctly, say, in
France I would still have to "translate" that in "aaaa/mm/jj", therefore I
would have to make a french version of my worksheet. And then a german one,
an italian one, and so on....

It would be great if MS implemented something similar to what they did with
numbers, so one could have written something like §§§§/^^/|| or whatever
symbol one may deem adequate for YEAR, MONTH, DAY.... but language
independent anyway!

I think the only way out is VBA..... but thank you anyway for your support!

 

[Bernard Liengme]

Ah, now I see the problem. I agree that MS must get into globalization.

By the way are you sure about "when I use DATEVALUE the date needs to be in
mm-dd-yyyy format in US and UK, while it's dd-mm-yyyy in other countries" ?
In Canada the official format is the same as the UK (although some people
use the US format and this makes for confusion). My Regional Setting as set
for dd/mm/yyyy. So the formula
=DATEVALUE("4/12/2009")
gives me 4-December not 12-April.

 

[zio69]

I cannot say for sure... but when I change my regional settings to english
(UK) or english (US) (sorry, I haven't tried any other english regional
setting), my formulas based on DATEVALUE do go crazy..... And of course so do
all other cells based on "ddd" formatting....

Oh, well.... looks like I will have to write down a VBA workaround!

Thank you anyway for your assistance.... I'm just an old mainframe guy and
excel isn't my daily bread! So you did help me by confirming there's no
workaround if I work with functions!

 

[Bernard Liengme]

While experimenting (a.ka. "messing about") I found these two formulas give
interesting results when A1 has a date
=TEXT(A1,"b") gives a number like 53 for 2009, 1 for 1900, 1000 for 1957, 0
for 1958
=TEXT(A1,"e") gives same as =TEXT(A1,"yyyy")
Wonder if the later is 'language-proof"

 

[Bernard Liengme]

Just a thought: you do know you can format a cell with custom formats like
"ddd" to show day of week? So in place of =TEXT(A1,"ddd") use =A1 and format
this cell as "ddd" . Since these are not really text, they should get
translated.

 

[zio69]

Yes, i did know about that but I actually needed to translate the cell to
text.

FYI, I've been messin' around too.... and what I found is rather
astonishing!!!
On the PC provided by my employer (using Office 2002), I've found that while
VBA in Access usually requires dates in MM/DD/YYYY despite of national
settings, in Excel it follows the national settings.... so no I have use for
VBA in this case! BUT DATEVALUE is more "flexible" than I thought and than
the help system suggests: in fact, feeding it with an ISO date (YYYY-MM-DD)
it works like a charm.... So, by using wisely DAY, MONTH and YEAR functions I
can alway send an ISO date to DATEVALUE... and at last get to the universal
internal format!!!! Cell formulas are no longer so easy to understand, but
what the hell! I won!

What's more astonishing, though, is that when I copied my worksheet (yeah,
i'm trying to write a universal, customizable calendar... with excel!) to my
home PC, where Office 2007 is installed.... I got a plethora of #VALUE?!!!
Even if the online help states that I should input date formatting in my
national format (i.e. gg/mm/aaaa), excel only accepts english constants....
so, when I changed all AAAAs to YYYYs and all GGGs to DDDs.... voilà!
Everything was fine and dandy again!

When my project is finished I will upload it to MS-office templates and
hopefully it will get published..... so you will see what we were talking
about!!

Thank you for your help and inspiration!

Best Regards,
Andrea (a.k.a. zio69, a professional cobol expert!)

 

[Bernard Liengme]

Thanks for feedback
BVL was once a COBOL programmer!