Date Formula - Common Mistakes
Updated: Aug 14, 2022
On Account Schedules the date formula is a powerful feature of Dynamics Business Central, yet it is easy to get it wrong. The most common place you see it are on recurring journals and account schedules, but it is used in many other places.
We all know how to add a month by writing +1M or a week by adding +1W. A convenient formula is CM for current month, and CW for current week, but what if you want the 1st of the month every time, no matter what the day or work date. Below you will find some common formulas I use. The trick is to calculate your required date based on the 1st of the month. This way you don't have to worry about a month with 30, 31 or February with 29 days in a leap year.
1st of Current Month
CM gives us the last day of current month.
+1D gets us to the first of the next month
-1M gets you to the first of the current month
1st of Prior Month
Like the formula above, but -2M gets you to the first of the previous month
Monday of Current week
CW gives us the last day of week. In BC that is Sunday
+1D gets us to the first day of next week
-1W gets you to Monday of the current week
Monday of Prior week
Similar to the formula above
1st, out 2 Months
1st, out 3 Months