Updated · Originally published
https://www.youtube.com/watch?v=P-sKltgOIek
In this blog post - we will be diving into the fascinating world of date formulas in D365 Business Central. Whether you're dealing with payment terms or recurring documents, understanding how to work with dates can greatly enhance your efficiency and accuracy in the system. Let's explore some straightforward examples to get you started!
Table of Contents
- Understanding Date Formulas
- Intermediate Date Formulas
- Resources for Learning Date Formulas
- Advanced Date Formulas
- Combining Date Formulas
- Conclusion
- Frequently Asked Questions (FAQ)
Understanding Date Formulas
Date formulas in Business Central provide a way to calculate dates dynamically based on various criteria. They can be used not just for payment terms but also for recurring documents, such as general journals and recurring sales documents. The beauty of date formulas lies in their flexibility and the different scenarios they can accommodate.
Basic Examples of Date Formulas
Let’s kick things off with a simple example: NET 30 Payment Terms. The calculation for this is represented as 30D. This tells Business Central to calculate a due date that is thirty days from the current date (Document Date). Whereas f you use D30, it will find the next 30th day of the month, regardless of today's date.
Another useful formula is 0D, which sets the due date to today (due upon receipt). You can also use 60D to extend the due date to sixty days from now. These formulas are intuitive and can be adapted based on your needs.
| Document Date | Payment Terms | Due Date Calculation | Due Date | Description |
| 3/3/2025 | NET 30 | 30D | 4/3/2025 | Due in 30 Days |
| 3/3/2025 | Next 30th | D30 | 3/30/2025 | Due on the Next 30th Day |
| 3/3/2025 | DOR | 0D | 3/3/2025 | Due Upon Receipt |
Intermediate Date Formulas
As we progress, let's delve into more complex scenarios. For instance, if you want to set a due date for the first day of the next month, you would use the formula 1M-CM. This formula tells Business Central to calculate one month from today and find the first day of that month.
| Document Date | Payment Terms | Due Date Calculation | Due Date | Description |
| 3/3/2025 | 1st D NM | 1M-CM | 4/1/2025 | Due the First Day of the Next Month |
| 3/3/2025 | LD NM | 1M+CM | 4/30/2025 | Due the Last Day of the Next Month |
Conversely, if you want the last day of the next month, you can use 1M+CM. This will yield the last day of the upcoming month. The CM component is crucial here, as it helps you navigate to the end of the month.
You can utilize +CM to find the end of the month and -CM to find the beginning of the month.
Additionally - These Current (C) Function can also be used with the below functions (Courtesy of )
| Letter | Meaning |
| C | Current |
| D | Day(s) |
| W | Week(s) |
| M | Month(s) |
| Q | Quarter(s) |
| Y | Year(s) |
Resources for Learning Date Formulas
To gain a deeper understanding of date functions, Microsoft provides excellent resources. Here are some helpful links:
Advanced Date Formulas
Now, let’s explore some more advanced formulas. The formula 15th NM can be used to set a due date for the fifteenth day of the next month. It calculates the end of the current month and then assigns the due date as the fifteenth day of the following month. This is particularly useful for businesses that operate on mid-month billing cycles.
| Document Date | Payment Terms | Due Date Calculation | Due Date | Description |
| 3/3/2025 | 15th NM | CM+D15 | 4/15/2025 | Due on the 15th Day of the Next Month |
| 3/10/2025 | 15th P 60 | D15+60D | 4/15/2025 | Due 60 Days from the Next 15th of the Month |
| 3/16/2025 | 15th P 60 | D15+60D | 5/15/2025 | Due 60 Days from the Next 15th of the Month |
Another intriguing formula is 15th P 60, which finds the next fifteenth of the month and adds sixty days to it. For example, if the invoice date is March 10, it will calculate the next fifteenth (March 15) and then add sixty days, resulting in a due date of May 15. However, if the invoice is posted just a few days later on March 16, it will look for the next fifteenth (April 15) and still add sixty days, leading to a due date of June 15.
Conclusion
We’ve only scratched the surface of what date formulas can do in Business Central. The examples provided are just the beginning, and there are countless combinations and scenarios to explore. As you become more familiar with these formulas, you'll find yourself able to manage dates more effectively across the system.
Thanks for tuning in today! If you have any questions or need assistance optimizing Dynamics 365 Business Central, feel free to reach out. I hope everyone has a fantastic day!
But Wait, There's More
Below - You will find a listing of examples of many different Microsoft Dynamics 365 Business Central Date Formulas and Examples - We hope they are helpful:
| Document Date | Date Calculation | Due Date | Description |
| 3/3/2025 | 30D | 4/3/2025 | Due in 30 Days |
| 3/3/2025 | D30 | 3/30/2025 | Due on the Next 30th Day |
| 3/3/2025 | 0D | 3/3/2025 | Due Upon Receipt |
| 3/3/2025 | 1M-CM | 4/1/2025 | Due the First Day of the Next Month |
| 3/3/2025 | 1M+CM | 4/30/2025 | Due the Last Day of the Next Month |
| 3/3/2025 | CM+D15 | 4/15/2025 | Due on the 15th Day of the Next Month |
| 3/3/2025 | CM+15D | 4/15/2025 | Due on the 15th Day of the Next Month |
| 3/3/2025 | D15+60D | 4/15/2025 | Due 60 Days from the Next 15th of the Month |
| 3/16/2025 | D15+60D | 5/15/2025 | Due 60 Days from the Next 15th of the Month |
| 3/3/2025 | CM | 3/31/2025 | Due on the Last Day of the Current Month |
| 3/3/2025 | -CM | 3/1/2025 | Due on the First Day of the Current Month |
| 3/3/2025 | CY | 12/31/2025 | Due on the Last Day of the Current Year |
| 3/3/2025 | CW | 3/8/2025 | Due on the Last Day of the Current Week |
| 3/3/2025 | -30D | 2/1/2025 | Due 30 Days Ago |
| 3/3/2025 | -D30 | 2/28/2025 | Due the Last 30th |
| Note: | The above goes to the 28th, if there are not 30 days in the month | ||
| 3/3/2025 | 1Y-CY | 1/1/2026 | Due the First Day of Next Year |
| 3/3/2025 | CY+1D | 1/1/2026 | Due the First Day of Next Year |
Frequently Asked Questions (FAQ)
What are date formulas in Business Central?
Date formulas are expressions used to calculate dates dynamically based on different criteria, such as payment terms or recurring documents.
How do I create a date formula?
A date formula is created using a combination of letters and numbers. For instance, 30D means thirty days from today, while 1M-CM calculates the first day of the next month.
Can I combine multiple date formulas?
Yes! You can combine different date formulas to create more complex date calculations that meet your specific business needs. A good example being D15 + 60D, meaning find 60 days after the next 15th of the month.
Where can I find resources to learn more about date formulas?
Microsoft provides several resources, including documentation and community blogs, to help you understand and utilize date formulas effectively.