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+1DorCW+WD1 | 9/30/13 (Monday) | Returns the Monday of Next Week | |
CW+5DorCW+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. |