top of page
Writer's pictureNosh

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+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


39 views0 comments

Recent Posts

See All

Comments


bottom of page