0

NP(Formula,..) shows wrong value when one field value blank

Jet Reports Historic Posts

November 19, 2014 06:40

None

Follow

Hello,

I am facing two issues while using the NP("Formula","[@[Order Date]]-[@Document Date]")to calculate the difference between two cells containing date values.

Now, in this scenario when the order date & document have a value, i.e. 01/10/14 & 10/10/14, then system shows it correctly.

In case, the Order date is 01/10/14 & document date is blank, then the diffencence is still shown with some value as -41950 and this value changes as the value changes.
It seems that in case one of the date is blank in the Purchase header, the system picks up some starting date and calculates difference accordingly.

Do I have to set a date in Jet Report/Excel to handle the situation where any of the fields would be blank?

Thanks in advance for your help.

• Facebook
• Twitter
• LinkedIn

4 comments

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

  Hi,
  
  why do you use "NP(Formular).." for that issue?
  Can't you do that directly in Excel?
  
  Which result do you expect or do you want to have, when one date is blank?
  
  regards

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

  Thanks for your reply.
  
  Actually, I have developed a report using table builder as I am a beginner in Jet Report.
  
  I tried calculating Day1(as below in the table) using the formula =NP("Formula","[@[BT Date]]-[@[PO Date]]")
  
  In case the BT date is blank, the system calculates and shows irrelevant values in column Day1:
  I tried using the following two formulas, but none of it worked
  =IF(J11=" " OR M11 =" ",NP("Formula","[@[BT Date]]-[@[PO Date]]")," ")
  =IF([@[BT Date]] = " "," ",=NP("Formula","[@[BT Date]]-[@[PO Date]]"))
  
  
  PR Approval Date BT Date Day1
  04/11/2014 41947.23904
  07/11/2014 41950.27425
  07/11/2014 41950.3341
  04/11/2014 04/11/2014 0.511840278
  11/11/2014 41954.29667
  
  Handling this with excel formula does not show any result.
  
  Can you suggest how this can be handled ?

• 0
  
  Jet Reports Historic Posts November 19, 2014 14:23

  HI,
  what about this?
  
  
  =IF(J11="" OR M11 ="",NP("Formula","[@[BT Date]]-[@[PO Date]]"),"")
  =IF([@[BT Date]] = "","",=NP("Formula","[@[BT Date]]-[@[PO Date]]"))
  
  withou a blank between the first " and the second "

• 0
  
  Jet Reports Historic Posts November 20, 2014 04:11

  Hi,
  
  I tried without giving space between the quotes , but it doesn't give the output.
  
  I have used table builder. And for calculating the difference between dates, will the excel formula work?
  
  Can you suggest a solution/work around ?