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....

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"zio69" <(E-Mail Removed)> wrote in message
news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
>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....

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" wrote:

> 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
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "zio69" <(E-Mail Removed)> wrote in message
> news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
> >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....
>
>
>

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.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"zio69" <(E-Mail Removed)> wrote in message
news:00BB84B6-C9AF-4840-9413-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "zio69" <(E-Mail Removed)> wrote in message
>> news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
>> >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....
>>
>>
>>

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" wrote:

> 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.
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "zio69" <(E-Mail Removed)> wrote in message
> news:00BB84B6-C9AF-4840-9413-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >> --
> >> Bernard V Liengme
> >> Microsoft Excel MVP
> >> http://people.stfx.ca/bliengme
> >> remove caps from email
> >>
> >> "zio69" <(E-Mail Removed)> wrote in message
> >> news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
> >> >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....
> >>
> >>
> >>
>
>
>

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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"zio69" <(E-Mail Removed)> wrote in message
news:E453139D-A203-47C9-A69A-(E-Mail Removed)...
>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" wrote:
>
>> 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.
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "zio69" <(E-Mail Removed)> wrote in message
>> news:00BB84B6-C9AF-4840-9413-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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
>> >> --
>> >> Bernard V Liengme
>> >> Microsoft Excel MVP
>> >> http://people.stfx.ca/bliengme
>> >> remove caps from email
>> >>
>> >> "zio69" <(E-Mail Removed)> wrote in message
>> >> news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
>> >> >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....
>> >>
>> >>
>> >>
>>
>>
>>

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.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"zio69" <(E-Mail Removed)> wrote in message
news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
>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....

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" wrote:

> 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.
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "zio69" <(E-Mail Removed)> wrote in message
> news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
> >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....
>
>
>

Thanks for feedback
BVL was once a COBOL programmer!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"zio69" <(E-Mail Removed)> wrote in message
news:4173AF8D-68E0-485C-B980-(E-Mail Removed)...
> 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" wrote:
>
>> 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.
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "zio69" <(E-Mail Removed)> wrote in message
>> news:19606014-870C-4A41-A9CF-(E-Mail Removed)...
>> >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....
>>
>>
>>