Be a Date Wizard with Date Formulas

In this video, I demonstrate the concept of date formulas. Both with filter and with the CALCDATE function.

https://youtu.be/ldSSu-VUJ60

This is another video is my “Begging AL” series.

In this video, Erik explores the world of date calculations and date formulas in AL and Business Central. Despite being an “ancient” topic in the Dynamics/Business Central ecosystem, date formulas remain one of the most searched-for subjects — and for good reason. They’re powerful, expressive, and can save you from writing complex date manipulation code. Erik walks through the fundamentals using BC Script (his AL compiler built in AL) as a scratch pad, demonstrating everything from simple date arithmetic to accounting period filters and language-safe date formulas.

Why Date Formulas?

Erik’s motivation for this video came from an interesting discovery. During a marketing meeting at eFocus (the company he works for), the team noticed that several search terms involving “CalcDate” were generating significant traffic to their website. The source turned out to be their product WSFN — a portal customization tool for Business Central that exposes the CalcDate function. The takeaway: the internet clearly needs more information about date formulas in Business Central.

Dates Are Just Integers

The date type in Business Central is actually an integer behind the scenes. This means you can perform basic arithmetic directly on dates:

  • Today + 1 gives you tomorrow
  • Today + 7 gives you next week (same day)
  • Today - 7 gives you last week
  • Today - 365 gives you approximately one year ago — but beware of leap years! Since 2020 was a leap year, subtracting 365 from January 10, 2021 actually lands on January 11, 2020, not January 10.

Date Formulas in Filters

Where things get really interesting is when you use date formulas in filter expressions. Erik demonstrates this with a small program that sets a filter on customer ledger entries:

Using the filter syntax, you can write expressions like:

  • Today + 7D — Seven days from today
  • CQ (Current Quarter) — Returns the ending date of the current quarter
  • CQ - 5D — Five days before the end of the current quarter, useful when something needs to be posted near (but not at) quarter end

Date Ranges

Date formulas can also return ranges, which is incredibly useful for filtering:

  • Week — Returns the current week’s date range (Monday through Sunday by default)
  • Quarter — Returns the full date range of the current quarter
  • P1 — Returns the first accounting period (January in standard calendar-year setups)
  • P5 — Returns the fifth accounting period

An important note about accounting periods: the P tokens respect your accounting period setup. If your periods are bimonthly, quarterly, or your fiscal year ends on a non-standard date (like December 1st), the period references will adjust accordingly.

Special Keywords

Business Central also supports some wonderfully readable date tokens:

  • Monday, Tuesday, etc. — Returns the specific day of the current week
  • Yesterday and Tomorrow — Exactly what they sound like
  • Yesterday..Tomorrow — A filter range from yesterday to tomorrow, which Erik notes has excellent readability

Work Date vs. Today

In addition to Today, there’s Work Date. The work date can be set independently of the actual calendar date, allowing you to adjust the base of your calculations. For example, Work Date + 14D gives you 14 days from whatever the work date is set to — useful in demo environments or when processing entries for a different date context.

The CalcDate Function

The CalcDate function is where date formulas really shine in AL code. You can compose complex date expressions that read almost like natural language:

CalcDate('CQ+1M-10D', Today) means: take today’s date, find the end of the current quarter, add one month, then subtract 10 days.

Erik’s advice: read your date formulas out loud. “Current quarter plus one month minus ten days” is immediately understandable, even for non-developers. This is far more intuitive than manually deconstructing dates, adding to months, and rebuilding date values.

Language Independence with Angle Brackets

One crucial detail: date formulas are language-dependent. The letter D stands for “days” in English, but in other languages it might be a different character. Similarly, keywords like Today, Current Month, etc., have translations.

To make your code work reliably across all languages, wrap date formula strings in angle brackets:

// Language-dependent (may break in non-English environments):
CalcDate('10D', Today)

// Language-independent (works everywhere):
CalcDate('<10D>', Today)

Both produce the same result in English, but the bracketed version is guaranteed to work regardless of the user’s language setting. Always use angle brackets in production code.

Creating Custom Date Filter Tokens

Business Central also allows you to extend the date filter system with your own custom tokens using the OnResolveDateFilterToken event. Here’s an example from the source code that creates a custom “party” token representing 10 days from today:

codeunit 50100 "DateFilters"
{
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Filter Tokens", 'OnResolveDateFilterToken', '', true, true)]
    local procedure MyFilters(DateToken: Text; var FromDate: Date; var ToDate: Date; var Handled: Boolean)
    begin
        if DateToken.ToLower() = 'party' then begin
            FromDate := Today() + 10;
            ToDate := FromDate;
            Handled := true;
        end;
    end;
}

With this extension installed, users can type “party” into any date filter field and it will resolve to 10 days from today. This pattern is incredibly powerful for creating domain-specific date shortcuts. Imagine tokens like “fiscal-year-end”, “next-payroll”, or “tax-deadline” that resolve to meaningful dates in your specific business context.

Summary

Date formulas in Business Central are a powerful tool that removes significant complexity from date manipulation in AL code. Here are the key takeaways:

  1. Don’t reinvent the wheel — Instead of manually deconstructing and rebuilding dates, use date formulas and CalcDate.
  2. Read formulas out loud — If it makes sense spoken aloud (“current quarter plus one month minus ten days”), it’s correct and maintainable.
  3. Use angle brackets — Always wrap date formulas in < > to ensure language independence.
  4. Leverage built-in keywords — Today, Work Date, CQ, CM, CW, CY, period references (P1–P12), day names, Yesterday, Tomorrow, and week/quarter/year ranges are all at your disposal.
  5. Accounting periods matter — Period tokens respect your fiscal calendar setup, making them ideal for financial reporting logic.
  6. Extend with custom tokens — Use the OnResolveDateFilterToken event subscriber to create business-specific date shortcuts.

These functions are specifically designed to cater to normal accounting scenarios, so when your accountant says “it has to be posted on the last Thursday of the month,” you can compound date formulas (find the last week, then find the Thursday) rather than writing fragile date arithmetic code.

Picture of hougaard

hougaard