DATEDIF formula doesn't work on computer that uses different locale

**Timoluege** · 08-18-2011, 06:05 AM

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

**martindwilson** · 08-19-2011, 04:36 AM

ahh i just re read this
dates are just numbers the formatting/international settings have no bearing on a real date. so on both machines change the format of a1 to general ,what do you see in the formula bar?

**Timoluege** · 08-25-2011, 04:15 AM

Hi Martin,

thank you very much for getting back to me and apologies for taking so long to your post - it just took a few days until I was physically in front of the other organizations computer again.

What you are saying is exactly what I had suspected - the date format should simply be a "style" that is applied to underlying data and formatted according to the different settings. So the locale should not matter.

Anyhow: When I was at my colleagues', I made sure that everything was set to "general" and checked what the formula bar said: It was 01.01.2001, which is what I would expect. However the formula did not work.

Very strange.

Best regards,

Timo

**martindwilson** · 08-25-2011, 04:33 AM

if you format the cell as general that date should appear as 36892 so that indicates the date is text and not a real date number, its the original that needs changing once its changed to a real date it should work on any machine,
datedif works with text dates as long as they are in the correct format
on my machine 23/4/08 would work as / is my shortdate seperator but 23.4.08 wont
to fix the lot on the original select the column with dates in
then use data /text to columns/next/next
select date
then chose the format your date is in i.e dmy or mdy i cant tell from your sample 1.1.2001 which one you need
this will convert the text dates to real ones