How do I sum a date field?
12 comments
-
Harry Lewis 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.
-
Amy Becker I want to sum contract date from the Job table in an NF function, but it is not a numeric field.
Thanks!
-
Harry Lewis 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.
-
Amy Becker Really? Okay, that's how we are doing it now, just thought there had to be a better way. Thanks for your help!
-
Harry Lewis Hi Amy -
I did a little experimenting.
You could try this:
=NL("Sum","Job","=NF(,""Contract Date"")")
-
Amy Becker 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!
-
Harry Lewis 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?
-
Amy Becker 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.
-
Harry Lewis 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?
-
Amy Becker Name and number are the only things that are different. It is our custom field, though.
-
Harry Lewis 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","<>@@")
-
Amy Becker Yay! You're the best! It worked, thanks Harry!