Options

Difference date calc NAV <> Excel?

lyotlyot Member Posts: 202
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?

Comments

  • Options
    KisuKisu Member Posts: 381
    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
    K.S.
  • Options
    KisuKisu Member Posts: 381
    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.
    K.S.
Sign In or Register to comment.