0

Summing a date field

Amy Becker

May 23, 2018 15:59

Answered

Follow

How do I sum a date field?

• Facebook
• Twitter
• LinkedIn

12 comments

• 0
  
  Harry Lewis May 23, 2018 16:05

  Hi Amy -

  What are you trying to accomplish?

  While I can use Excel's SUM function to add up dates...

  I doubt that is what you are after.

  Edited by Harry Lewis May 23, 2018 16:05
• 0
  
  Amy Becker May 23, 2018 16:52

   

  I want to sum contract date from the Job table in an NF function, but it is not a numeric field.

  Thanks!

• 0
  
  Harry Lewis May 23, 2018 17:17

  Hello Amy -

  You would need to list out all applicable Contract Date and then use Excel's SUM() function to add them together:

  

   

  

  If desired, this can be done on a separate sheet and then have just the final sum appear on your main report page.

• 0
  
  Amy Becker May 23, 2018 17:20

  Really?  Okay, that's how we are doing it now, just thought there had to be a better way.  Thanks for your help!

• 0
  
  Harry Lewis May 23, 2018 17:48

  Hi Amy -

  I did a little experimenting.

  You could try this:

  =NL("Sum","Job","=NF(,""Contract Date"")")

• 0
  
  Amy Becker May 23, 2018 20:00

  It tells me it's not numeric.  I tried putting Value in front of it and Local and combinations of them, but none works.  Thanks a lot for your help!

• 0
  
  Harry Lewis May 23, 2018 22:11

  Are you using Dynamics NAV?  If so, what version?

  What version of the Jet Excel add-in are you using?

  What happens if you enter this function?

  =NL("Sum","Cust. Ledger Entry","=NF(,""Posting Date"")","customer no.","me")

  Does it return zero or an error?

   

  Edited by Harry Lewis May 23, 2018 22:11
• 0
  
  Amy Becker May 23, 2018 22:28

  Zero and if I put in a legit customer it returns the correct info.  I am on NAV 2016 and 18.0.18060.4 of Jet.

• 0
  
  Harry Lewis May 23, 2018 22:34

  Great test!

  Now...

  Using the Jet Browser...

  

  Navigate to your Cust. Ledger Entry table and, within there, the Posting Date field.  Expand the Details section of the Browser and make note of the settings.

  

  Now, do the same with the Job table and the custom Contract Date field.

  How do the details differ?

   

• 0
  
  Amy Becker May 23, 2018 22:40

  Name and number are the only things that are different.  It is our custom field, though. 

• 0
  
  Harry Lewis May 24, 2018 14:07

  Thanks, Amy.

  It sounds like there are blank entries in the Contract Date field.

  This can force either NAV or SQL to treat the entire results of a query as non-numeric.

  Instead of this...

  =NL("Sum","Job","=NF(,""Contract Date"")")

  Try this...

  =NL("Sum","Job","=NF(,""Contract Date"")","Contract Date","<>@@")

• 0
  
  Amy Becker May 24, 2018 14:27

  Yay!  You're the best!  It worked, thanks Harry!