Blog

Entering Date Formulas in Microsoft Dynamics NAV

By Chris Young

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 FormulaResulting DateDescriptionNotes
CW9/29/13 (Sunday)Returns the last date of the current week 
CM9/30/13 (Monday)Returns the last date of the current month 
CQ9/30/13 (Monday)Returns the last date of the current quarter 
D1510/15/13 (Tuesday)Returns the next 15th of the month 
D15 + 1M11/15/13 (Friday)Returns 1 month after the next 15th of the monthSame result as 1M + D15
1M + D1511/15/13 (Friday)Returns 15th day after the date is advanced 1 monthSame result as D15 + 1M
1M – 15D10/10/13Returns the date 15 days less than a full month after reference date 
1M + 15D11/9/2013Returns the date 15 days more than a full month after reference date 
CM +1D10/1/13 (Tuesday)Returns the 1st of the following month 
CM+1M10/30/13 (Wednesday)Returns the last date of the current month advanced by one monthIf the reference date had been 02/23/2013 the returned date would be 03/28/13
CM + D10 + 90D1/8/2014 (Wednesday)Returns a date 90 days after the next 10th of the month 
1M+CM10/31/13 (Thursday)Returns the last date of the following monthIf the reference date had been 02/23/2013 the returned date would be 03/31/13
CW+1DorCW+WD19/30/13 (Monday)Returns the Monday of Next Week 
CW+5DorCW+WD510/4/2013 (Friday)Returns the Friday of Next Week 
CW+1D+3W10/21/2013 (Monday)Returns 3 Weeks from Next Monday 
CW-1D9/28/2013 (Saturday)Returns Saturday of This WeekIf 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+WD110/21/13 (Monday)Returns the Monday after the next 15th of the month 
WD4 + 6W11/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.
Chris Young

Chris Young

Partner, Clients First Business Solutions New Jersey

Chris Young is your go-to resource for all things Dynamics NAV and Dynamics 365 Business Central at Clients First. Highly skilled in ERP solution architecture and financial planning, Chris has spent the last 30 years helping clients select and implement the right financial, manufacturing, and distribution software for their needs. When he’s not presenting to captivated crowds or sharing his knowledge through product demos, you can find Chris in the garage working on a car or fixing something around the house.