Difference date calc NAV <> Excel?

lyot

I need to recreate some calculations from an Excelfile into NAV.

Excel:
Cell =29/10/10 but represented as a number so it shows 40480

NAV:
I found out that Excel internally works with date 01/01/1900 for all it's date calculations.

EVALUATE(dat,'01011900');
MESSAGE('%1',291010D - dat);

However.. the result from this calculation is 40478. ](*,)
What is even more strange is that when I do a Count on the Date table in NAV with filter: 01011900..291010
the result is... 40479 ](*,)

Any advice?

Kisu

lyot wrote:

I need to recreate some calculations from an Excelfile into NAV.

Excel:
Cell =29/10/10 but represented as a number so it shows 40480

NAV:
I found out that Excel internally works with date 01/01/1900 for all it's date calculations.

EVALUATE(dat,'01011900');
MESSAGE('%1',291010D - dat);

However.. the result from this calculation is 40478. ](*,)
What is even more strange is that when I do a Count on the Date table in NAV with filter: 01011900..291010
the result is... 40479 ](*,)

Any advice?

There is definitely something fishy with the date calculation. I tried with closer days and got proper result on Nav and Excel
Same result on Nav and Excel

MESSAGE('%1',100611D - 010611D);
Resulted 9

and also with Excel
0 | A | B | C
1 | 10.6.2011 | 1.6.2011 | 9

C1 = A1-B1

Kisu

The problem might be in Excel, I did few day tests on both platforms, Navision can calculate even beyond 1890 as Excel stops 1900

I hope this helps.