I have an excel file that I regularly have to share with a different organization. Windows in my organization is set up to be "French (Switzerland)" under "Regional and Language settings", Windows in the other organization is set up as "English (US)".
One of the things we are sharing through Excel are dates of birth and age of our cases. We are using Excel 2002, they are using Excel 2007.
A1: 1.1.2001
B1: =DATEDIF(A1;TODAY();"y")
This work perfectly when I open my file on my computer, but when I send the file to my colleagues in the other organization and they open the files on their computer, B1 returns an error #VALUE. I.e. the file that I sent has a function that works or doesn't work depending on which computer it is being opened.
When I go to "Format Cells" I can see that the format for A1 is "date", "*14.03.2001" and the locale "French (Switzerland)". I thought the asterix in front of the date would actually prevent any problems from occurring, but obviously not.
When I manually changed the date on my colleagues computer to the US format, the formula worked.
How can we work around this? Simply changing the locale of A1 to "English (US)" is not really a workaround since we have to exchange data with more than one organization from different countries, so there is no knowing what set-up they have on their machines.
Thank you very,
any help is very much appreciated,
Timo
Bookmarks