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+1D-1M | 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 | CM+1D-2M | Like the formula above, but -2M gets you to the first of the previous month |
Monday of Current week | CW+1D-1W | 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 | CW+1D-2W | Similar to the formula above |
1st, out 2 Months | CM+1D+1M | |
1st, out 3 Months | CM+1D+2m | |
Comments