Mastering Date Formulas in Business Central: Rules, Variables, and Fun with Time!

If you’ve spent any time in Microsoft Dynamics 365 Business Central, you know that date formulas are the secret sauce to streamlining many processes. Whether you’re filtering reports, automating tasks, or just trying to look super smart in front of your coworkers, date formulas are here to save the day. Let’s break it all down!

---

The Basics: What Are Date Formulas?

Date formulas in Business Central are shorthand tools that help you calculate and manipulate dates. They’re not just for techies – anyone can use them to filter lists, define payment terms, or control report parameters. Once you learn the rules, you’ll wonder how you ever lived without them!

---

Common Rules of Date Formulas

1. Every Formula Starts from a Base Date
   • This could be today (system date), a posting date, or another field.
   • Pro Tip: If you don’t specify a base date, Business Central often assumes it’s today.
   • Formulas are processed left to right and do not follow arithmatic rules, for example CM+10D is not the same at 10D+CM
2. Work Date vs. System Date
   • The work date is a customizable date that users can set for their session. It can be used by calling out the variable WD instead of using ‘today’
   • Some formulas do not play well with WD so I usually spell it out as workdate if I get an error.
   • To set the work date: Go to Settings (Gear in Top Right) > My Settings > Work Date and update it as needed.
   • Pro Tip: If you don’t set a work date, Business Central defaults to the system date.
   • PRO PRO Tip: Using P (Or Period) uses Work Date by default.
3. C Means Current
   • The letter “C” always points to the current period, but here’s the kicker: it means the last day of that period.
     • CM = Last day of the Current Month.
     • CY = Last day of the Current Year.
     • CW = Last day of the Current Week.
4. Operators Move Time Forward or Back
   • Use + to move forward and – to move backward.
     • Example: CM+10D means 10 days after the last day of the current month.
     • Example: CW-2D means 2 days before the last day of the current week.
5. Time Units Are Simple (Sort of)
   • D = Days OR Day of the Month (See Item #6 below)
     • Don’t Spell it out – the rules are wonky and it’s just easier to NOT spell this out
   • W = Weeks OR Week of the Year OR Work Date – See Additional details of W Function
     • Week is Monday through Sunday and that is also the day count (Monday = 1st Day, so W+2D
6. M = Months (You can also spell out “Month” but you cannot use M alone or the system will think you mean Monday)
   • Y = Years (You can also spell out “Year”)
   • Q = Quarters (You can also spell out “Quarter”)
   • P = Period (You can also spell out “Period”)
     • Example: If you always want a list to show current month, save it’s filter to just say ‘month’
7. Placing “D” Before or After Numbers
   • Putting “D” after a number specifies the exact number of days.
     • Example: Today+10D = 10 days from today.
   • Putting “D” before a number defines the next occurrence of that number DAY of the Month.
     • Example: If today is 01/15/2024, today+D1 = Next occurrence of the 1^st = 2/1/2025.  If we use today+D18, it will not be February 18^th, because the next 18^th is January 18^th.  Got it?
8. Placing “W” Before or After Numbers (See “W Function Explained”)
   • Putting “W” after a number specifies the exact number of weeks.
     • Example: Today+1W = 1 week from today.
   • Putting “W” before a number defines the next occurrence of that Week Number (for the year).
     • Example: If our base date is 01/15/2024, today+W1 = Next occurrence of Week #1 is 12/29/2025 (remember, weeks start on Mondays).  If our base date is 12/01/2024 and we use Workdate+W1 we would get the next Week 1 after 12/02/2024 which is 12/30/2024. It’s tough, I know.
9. Ranges Are Written with ..
   • Use two periods (..) to define a range.
     • Example: TODAY..CM = From today through the last day of the current month.
     • Example: CM..CY = From the last day of the current month through the last day of the current year.
10. Complex Chains Are Allowed
    • Combine elements for more precise results.
      • Example: CM+1M-3D = 3 days before the last day of the next month.
      • Example: CQ+1Q+15D = 15 days after the last day of the next quarter.
11. P or Period Works Just A Bit Differently
    • Using the formula P or Period uses the Work Date by default and NOT the system date like other formulas. When using P1 or P2 type of Formulas, they are referring back to the periods setup in the Account Period window in the system.

---

Common Variables and What They Mean

Here’s your cheat sheet for the most popular variables:

Variable	Meaning	Example
Today	Today	Today+10D (10 days from today)
CW	Last day of the current week	CW-3D (3 days before week-end)
CM	Last day of the current month	CM+5D (5 days after month-end)
CQ	Last day of the current quarter	CQ-7D (7 days before quarter-end)
CY	Last day of the current year	CY+1M (1 month after year-end)

---

Real-Life Use Cases

1. Filter Sales Orders Due This Month
   • Formula: TODAY..CM
   • Meaning: All orders due from today through the last day of this month.
2. Set Payment Terms to Month-End + 15 Days
   • Formula: CM+15D
   • Meaning: Payment is due 15 days after the last day of the month.
3. Find Late Payments
   • Formula: ..Today-1D
   • Meaning: All invoices due before today.
4. Plan for the Next Quarter
   • Formula: CQ+1Q
   • Meaning: Last day of the next quarter.
5. Show List of Current Period Transactions
   • Formula: Period
   • Meaning: Will automatically adjust o the current period based on Work Date (Period uses Work date, not system date, by default)

---

Make It Fun

Think of date formulas like a time machine. With a little practice, you can jump to any day, week, month, or year without ever leaving your chair.

Still feel like you need a flux capacitor? Don’t worry – the best way to learn is by experimenting. Open a filter pane, enter a formula, and see the magic happen. You’ll be bending time like a pro in no time!

---

Final Tip: Bookmark This Guide

If you’re new to date formulas, save this guide for quick reference. And remember, the more you use them, the faster you’ll get.

Now, go give it a shot and save yourself a ton of time… one date formula at a time!

Here is some additional examples and use cases… try them.  Did they work?  Comment below?  Need one that you don’t see?  Comment below!  Love to hear from you!

OPERATORS EXPLAINED

---

---

FUNCTIONS EXPLAINED

---

---

T Function Explained

W Function Explained

WD Function Explained

D Function Explained

Y Function Explained

Date Formulas in Business Central

This document showcases a comprehensive list of date formulas in Microsoft Dynamics 365 Business Central. These formulas can be used for filtering, calculations, and workflows to make your processes more efficient.

Functions Explained (Examples below this table)

SHAWN’S RUNNING LIST OF EXAMPLES

---

---

Thanks for reading!

Shawn Dorward

Microsoft MVP, Business Applications

LifeHacks365.com | LinkedIn | Twitter

COMMENT BELOW

---