Mastering Date Formulas in Business Central: The Secret Weapon for Smarter Filters and Scheduling

If you’ve ever filtered a report, set up a recurring journal, or defined a payment term in Business Central, you’ve probably stumbled across date formulas. And if you’re like most users, you might have squinted at the field, muttered something about cryptic codes, and hit Enter anyway—hoping for the best.

Let’s change that.

In this guide, we’re going to demystify Business Central date formulas. You’ll learn:

• What they are and how they work
• All the components you can use
• Real-world examples from journals, reminders, reports, and filters
• Bonus tips for avoiding common mistakes (like the infamous 1W = 8 days gotcha)

Once you get the hang of it, date formulas become a powerful tool to help you automate, calculate, and filter dates more precisely—with less clicking and more clarity.

---

What Is a Date Formula in Business Central?

A date formula is a short string of letters and numbers used to calculate a date relative to today or another date.

You’ll find date formulas throughout the system, including:

• Recurring journals → to define how often an entry posts
• Payment terms → to calculate due dates
• Reminders & finance charges → to define grace periods or interest start dates
• Report filters → to quickly pull “last quarter” or “next 30 days”
• Workflow conditions → to trigger based on date differences

---

Core Rules to Know

• Today counts as day 1.
  If you enter 1W, it’s actually 8 days including today. To get exactly 7 days, use 6D or 1W-1D.
• Formulas can include up to 20 characters.
  They are case-insensitive, but stick to uppercase for clarity.
• You can combine formulas using + or – signs.
  For example, CM+10D means 10 days after the start of the current month.

---

Date Formula Components

Here are the valid abbreviations you can use in formulas:

Letter	Meaning
C	Current
D	Day(s)
W	Week(s)
M	Month(s)
Q	Quarter(s)
Y	Year(s)

---

Common Patterns and What They Do

Number + Unit

Formula	Meaning
10D	10 days from today
2W	2 weeks from today
3M	3 months from today
1Y	1 year from today

Unit + Number

Formula	Meaning
D10	The next 10th of the month
WD4	The next Thursday (4 = Thu)
WD1	The next Monday (1 = Mon)

Current Period

Formula	Meaning
CW	First day of the current week
CM	First day of the current month
CQ	First day of the current quarter
CY	First day of the current year

Note: These typically calculate from the start of the period.

Combined Formulas

Formula	Meaning
CM+10D	10 days after the start of the current month
CQ-1D	Last day of the previous quarter
CW+1W	Start of next week
CM+1M-1D	Last day of the current month
-1Q	One quarter ago from today

---

Real-Life Examples You’ll Actually Use

• Recurring Journal Frequency
  1M – Post every month
  1W – Post every week (but remember it includes today!)
• Payment Term Calculation
  30D – Net 30
  CM+15D – Due on the 15th of the current month
• Reminder Grace Period
  7D – Wait 7 days after due date before sending reminder
  CQ+5D – 5 days after the start of the current quarter
• Date Filters on Reports
  ..TODAY – Everything up to today
  -1W..TODAY – Last week to today
  CM..CM+1M-1D – All days in the current month

---

Tips, Tricks & Gotchas

TODAY COUNTS

Want a “true” week from today? Use 6D instead of 1W.

4TH OF THE WEEK ≠ 4TH OF THE MONTH

WD4 means the next Thursday, not the 4th day of the month.

DON’T STACK TOO MANY CHUNKS

Keep it simple—combine no more than 2–3 formula parts or you’ll risk confusion (or errors).

LEVERAGE FOR DYNAMIC FILTERS

In reports, saved views, and APIs, use formulas to build dynamic date ranges that update without editing the filter.

---

FAQs About Date Formulas in Business Central

Q: Why do I get the error “There is nothing to post because the journal does not contain a quantity or amount”?
A: This often happens in recurring journals if the Recurring Frequency formula is invalid or the Amount field wasn’t correctly updated for the recurrence. Make sure:

• You’re using a valid date formula (e.g., 1M, not 30)
• You have an amount or quantity entered (depending on type)
• Your Posting Date is still within the allowed period

---

Q: Can I use date formulas in custom fields or extensions?
A: Yes, many customizations that allow date logic will support formulas. But test thoroughly—some fields require absolute dates, not calculated ones.

---

Q: Can I calculate backwards from a specific date?
A: You can, but it depends on context. For filters, -1M..TODAY works. For fields like “Due Date Calculation”, use -30D to go backward from the document date.

---

Q: Is there a cheat sheet or quick reference for these?
A: Absolutely—bookmark this blog or check back for our coming download: BC Quick Reference: Keyboard shortcuts, date formulas and more.

---

Want More Business Central Tips?

Subscribe to Dynamics Power Play for practical, no-fluff guides to getting more out of Business Central. Whether you’re new to the platform or just want smarter ways to work—this is where you’ll find them.