Back to the Basics in NAV (Navision)

Many fields in NAV use date formulas as the required value. Some examples include Lead Time Calculation (vendors, items, stockkeeping units), Expiration Calculation (item), Due Date Calculation (payment terms). Basic date formulas are straight forward and include such examples as 10D, 3W, or 1M, resulting in dates 10 days, 3 weeks, and 1 month after our reference date. (Think of the reference date as the document date of an invoice to calculate the due date, or the order date of a purchase order when calculating the expected receipt date). But, you may have noticed the date formula fields will accept more characters than the basic example use, letting us wonder how we might use the field. The following list are examples of more advanced uses of the date formula fields. (The list does not cover every possible combination. Go ahead and try a few on your own). Assume for each of the examples a reference date of Monday September 23, 2013.

Date Formula Resulting Date Description Notes
CW 9/29/13 (Sunday) Returns the last date of the current week
CM 9/30/13 (Monday) Returns the last date of the current month
CQ 9/30/13 (Monday) Returns the last date of the current quarter
D15 10/15/13 (Tuesday) Returns the next 15th of the month
D15 + 1M 11/15/13 (Friday) Returns 1 month after the next 15th of the month Same result as 1M + D15
1M + D15 11/15/13 (Friday) Returns 15th day after the date is advanced 1 month Same result as D15 + 1M
1M – 15D 10/10/13 Returns the date 15 days less than a full month after reference date
1M + 15D 11/9/2013 Returns the date 15 days more than a full month after reference date
CM +1D 10/1/13 (Tuesday) Returns the 1st of the following month
CM+1M 10/30/13 (Wednesday) Returns the last date of the current month advanced by one month If the reference date had been 02/23/2013 the returned date would be 03/28/13
CM + D10 + 90D 1/8/2014 (Wednesday) Returns a date 90 days after the next 10th of the month
1M+CM 10/31/13 (Thursday) Returns the last date of the following month If the reference date had been 02/23/2013 the returned date would be 03/31/13
CW+1D

or

CW+WD1

9/30/13 (Monday) Returns the Monday of Next Week
CW+5D

or

CW+WD5

10/4/2013 (Friday) Returns the Friday of Next Week
CW+1D+3W 10/21/2013 (Monday) Returns 3 Weeks from Next Monday
CW-1D 9/28/2013 (Saturday) Returns Saturday of This Week If the reference date had been 09/29/2013 (Sunday) the returned date would still be 09/28/2013. The week starts on Monday in NAV.
D15+WD1 10/21/13 (Monday) Returns the Monday after the next 15th of the month
WD4 + 6W 11/7/13 (Thursday) Returns the Thursday 6 weeks after the next Thursday. Imagine a vendor that ships only once a week with a long time in transit.