Entering Date Formulas in Microsoft Dynamics NAV

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.

2013-10-16T16:19:00+00:00 October 16th, 2013|General ERP articles|Comments Off on Entering Date Formulas in Microsoft Dynamics NAV