Convert DATE to FORMAT 1900 (Excel)

BlackMagic
BlackMagic Member Posts: 38
edited 2008-11-15 in NAV/Navision Classic Client
Hi guys.

For integration beetween a route planning system and Dynamics NAV5 I need to export a CSV file containing a delievery date. But the target software want the date presented in FORMAT 1900 (Excel format). This is an integer presented the quantity of days after 01/01/1900.

Eg. 14/11/08 in FORMAT 1900 got the value: 39767.

Can anyone tell me an easy way to do this conversion?

I have try Google for an algorithm or an automation but I didnt find any usable, and I think developing an algorithm by my own would take too long time.

So my best idea I could come to is exporting the date value from NAV to a cell in Excel file (eg. A1) and make a formula in cell B1 doing the conversion, then I could import the value from this field.

But I hope any know an easier way like using an automation or an algorithm.

Thanks for advance.

Comments

  • kapamarou
    kapamarou Member Posts: 1,152
    In Nav Isn't Date - Date = Integer?

    Also you could use the Date Virtual Table as follows:


    DateRec.Setrange(Type,DateRec.Type::Date);
    DateRec.Setrange(PeriodStart,01011900D,MyNewDate);
    DatRec.COUNT;
  • BlackMagic
    BlackMagic Member Posts: 38
    Very nice solutions! Thank you alot :).
  • kapamarou
    kapamarou Member Posts: 1,152
    You're welcome. :D
  • David_Singleton
    David_Singleton Member Posts: 5,479
    kapamarou wrote:
    In Nav Isn't Date - Date = Integer?

    I think stick with the integer, thats the best idea.

    ExcelDate := NavDate - 12311899D; (or somethign like that)
    David Singleton

Categories