In today’s blog, we’re going to talk about date formulas, as they are used in many areas of in Microsoft Dynamics 365 Business Central (hereafter BC), such as calculating invoice due dates, determining product expiration dates, setting the frequency of recurring journals, and more.
Date formulas in CALCDATE
I want to show how date calculations are performed through code, sharing information gathered from various sources about calculating dates using dateformula and calcdate , since they are widely used. This can be especially helpful for those who are new to Business Central.
We will begin by listing the different expressions that can be used in a Date Formula field.
After that, we will combine them to create more complex expressions, and finally, we will provide some examples of date calculations that may be useful.
Basic CALCDATE Syntax
Calculates a new date based on a date expression and a reference date.
NewDate := CALCDATE(DateExpression [, Date])
Common parameters used in CALCDATE
It may look complicated, but it’s actually quite simple.
Combinations
2D = 2 days from today 3W = 3 weeks from today D12 = The next 12th day of a month WD5 = The next 5th weekday (Thursday) CM+20D = Current month + 20 days -1Y = 1 year ago from todayCalculating due dates with CALCDATE
Example: first and last days First day of the month: Last day of the month: Last day of next month:CalcDate
NewDate := CALCDATE(DateExpression [, Date]) DateExpression = [][][] = [] = + | – = | | = A positive integer = D | WD | W | m | Q | Y (D = day, WD = weekday, W = week, M = month, Q = quarter, Y = year) C (C=current) These rules show that date expressions consist of zero, one, two, or three sub‑expressions. Each sub‑expression consists of an optional sign and a term. The following are some typical examples of terms: 30D → 30 days (corresponds to "Number"-"Unit" ) WD2 → Weekday number 2 (corresponds to "Unit"-"Number" ) CW (current week; corresponds to "Prefix"-"Unit") The internal Dynamics NAV calendar starts on Monday and ends on Sunday. This means that Monday is considered weekday 1 and Sunday is weekday 7.Implementing CALCDATE in Visual Studio Code
- In this CALCDATE example for Business Central, , we calculate a due date based on the current day (TODAY) and add one day to it.
- In the following example, we use the GETRANGEMIN function to retrieve the earliest date within a date range (“Date Filter”). From that date, we then calculate a new value by adding three months and subtracting one day.
- In this example, we create a new page, and in the trigger that runs when the page opens, we include the following code, which calculates three different dates:
Additional Resources on CALCDATE in Business Central
At the following Microsoft link, you can find a detailed description of the CALCDATE function, which allows you to perform date calculations based on a given date and a specific date expression: https://learn.microsoft.com/es-es/dynamics-nav/calcdate-function–date-
I hope you find it helpful. If you enjoyed this article, you may also want to read: How to Change Item Dimensions in Dynamics 365 Business Central (2024).Dynamics Programming – ABD Consulting and IT Solutions