Convert DATE to FORMAT 1900 (Excel)

BlackMagic

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.

kapamarou

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

Very nice solutions! Thank you alot .

kapamarou

You're welcome.

David_Singleton

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)