Date & Time formula translation error

[eric.d]

Hi all,

I'm having a problem with some date & time formulas.

At work, some people are using an english version of excel, while others are using a french version. I've never hade any trouble before with my formulas until now. I'm using the following formulas on an english version :

- workday;
- networkdays;
- weeknum

which translate correctly when I open the workbook in french to serie.jour.ouvre, nb.jours.ouvres and no.semaine respectively. The problem is when I save and open the workbook again on an english version, those formulas stay the same and won't translate, and give me a #name error.

I wonder if anybody as ever encoutered this before, and if so, how can I work around?

Thank you.

 

[venkat1926]

If regional configuration of dates in your computer ins American then you havae to convert English dataes to american dates and the operatge.use this forfmula
suppose A1 has date (english date) as 28/03/2013 it will be a string
formula in B1
=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4))+0

will give you

3/28/2013 an american date. thkere are many other formulas

 

[eric.d]

Ok, so if anyone ever comes accross the same problem, here is the cause for my particular case.

Before excel 2007, those formulas were part of the Analysis tool pack, and the add-on had to be activated on every computer opening the workbook. We have excel 2007, in which those formulas are not an add-on anymore, but it still didn't work.

The thing is, the workbook was saved in an Excel 97-2003 format, and not Excel 2007, which prevented the formula to translate correctly. I just had to save it in the right format, and now it works perfect.