0

Convert a Datetime field in NAV to Date in Excel

Jet Reports Historic Posts

November 19, 2014 06:49

None

Follow

Okay - if I was right that you were trying to work in a table, then yes, it is possible to wrap your formula into the Table context.

What I did was to add the date field I wanted to convert, and then add a formula field to convert the date (yes, you end up with an extra column, but you can probably hide that in some way).
My formula (as typed) was =INT([@[Date Created]])
The final output formula looked like: =NP("FORMULA","INT([@[DATE CREATED]])")

So I'm guessing that your formula should be: NP("Formula","INT([@[BT Date]])-INT([@[PO Date]])")

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts November 19, 2014 06:49

  Hello,
  
  I have a field "Last Modified Date" in Approval Entry in Navision. I need this date to calculate the difference with the Purchase Order Date.
  
  The problem is that Last Modified Date field data type is Datetime. Now when I use it in NP("Formula",["@[Last Modified Date]]-[@[Order Date]]"], it calculates incorrectly as the data type of Order date is Date.
  
  Can I convert the data type of "Last Modified date" to Date type in Excel.
  
  Thanks in advance for help.

• 0
  
  Jet Reports Historic Posts November 19, 2014 12:54

  Hi,
  
  have you tried to use the Excel-Formular "=Value()" to convert the date and date-time to a number?
  with date-time you will get an number with decimalplaces, but maybe you can round the result to an integer.
  
  is that the right direction for you?
  
  regards
  jetsetter

• 0
  
  Jet Reports Historic Posts November 19, 2014 13:19

  This is the formula I have used in the design is to calculate the difference between two dates:
  NP("Formula","[@[BT Date]]-[@[PO Date]]")
  
  Adding the Value function to this gives a #VALUE error.
  
  How can this be handled ? Am not sure how to add excel formulas.

• 0
  
  Jet Reports Historic Posts November 20, 2014 22:39

  I think that INT is the better formula to wrap your values in - that would convert both numbers to date only.
  
  The trick is, I'm not sure you can wrap something like that into a table formula (which I'm guessing is what you're doing here looking at the syntax).
  If I were doing it with a regular NL formula it would look something like this: INT(NL(,"CUST LEDGER","INVOICE DATE",filters here…)), and I would use that to convert both numbers to the INT format- then format the cells as short date to display a date instead of Excel's date code. Once both cells have the same type of data, your subtraction would be easy.
  
  I'll see if I can't carve out some time to build a table to test tomorrow - but I don't work in them often.

• 0
  
  Jet Reports Historic Posts November 26, 2014 07:27

  Hi
  
  Try using the below excel formula and change the format of the cell to Date..
  
  Date and Time
  30/08/2008 16:28
  
  Converted to date
  =DATE(YEAR(C12),MONTH(C12),DAY(C12))
  
  Regards
  Alex… : :P

• 0
  
  Jet Reports Historic Posts December 01, 2014 10:13

  Thanks Heather and Alex for the help.
  
  I used the Jfx to develop the report & using excel formula solved the problem.