Date Equations


A Date Equation is like a mathematical equation, except for dates. For example, d+1. The value that is passed into the date equation is typically the value of today’s date, and the date equation outputs a new value. In the example, if today is Jan 1, then: (Jan 1) → d + 1 → (Jan 2). In other words, this date equation adds one day to the input date. Any token that adds or subtracts a value to the current date is known as an adjustment token.
Date Equations also have the concept of business days. For example, Friday → bd + 1 → Monday. In other words the weekend days Saturday and Sunday are skipped. In this case, “bd” stands for business day. In the example, it’s possible that Monday was a holiday, so then the output would actually be Tuesday.
This brings up the point that in order for business day tokens to work correctly, they have to know which days are holidays and non-working days (e.g. weekends). Whenever a date equation is used that contains a business day token, then a Calendar must also be specified that contains the definitions of the holidays and non-working days. Users can define their own Calendars, for example, the United States and Canada have different sets of holidays. Even within Canada, BC celebrates a different Family Day than Ontario. Several default calendars are automatically defined when the software is first installed.
Another type of token is an alignment token. For example, mfd, stands for month first day. There is also mfbd which stands for month first business day. Example, Jan 15 → mfd → Jan 1.

Alignment tokens and adjustment tokens can be chained together, separated by commas. E.g. mfd,bd-1 would output the last business day of the previous month.

Let’s take Victoria Day as a real example. Victoria Day is celebrated on the last Monday before May 25. We need a function f(x) such that f(today) = Victoria Day.

  • The first step is to align to the start of the year, using the year first day (yfd) alignment token: f(x) = yfd
  • This will always produce Jan 1 (of the current year). Note, the “x” variable is implicitly passed to the tokens, so yfd is really yfd(x) which also changes the value of “x” when it is passed to the next token.
  • Next we need to get to the month of May. Since we are in January we add four months to get to May: f(x) = yfd,m+4
  • This will always produce May 1 (of the current year).
  • Next we need to get to the 25th day of May. Since we are on May 1, we need to add 24 days: f(x) = yfd,m+4,d+24
  • Now we need the last Monday, so we use the previous Monday alignment token (pMon). The final date equation is: f(x) = yfd,m+4,d+24,pMon

Here are some other commonly used date equations:

Equation	Description
d+0		Today.
d+1		Tomorrow.
d-1		Yesterday
d+2		Day after tomorrow.
bd-1		Previous business day.
w+1		Add 7 days.
w-1		Subtract 7 days.
m+1		Add 1 month to the current month. The day of month is
		adjusted down if it exceeds the last day of that month.
m-1		Subtract 1 month from the current month. The day of month is
		adjusted down if it exceeds the last day of that month.
y+1		Next year.
y-1		Previous year.
q+1		Next quarter.
q-1		Previous quarter.
m-1,mld		Last day of the previous month.
m-1,y-1,mld	Last day of previous month one year ago.
y-1,mfd		One year ago, first day of this month.
q-1,qld		Last day of the previous quarter.
y-1,qfd		First day of this quarter, one year ago.

Our software provides a hyperlink to a reference guide containing all the tokens. Here is a partial screenshot of a input field:

Clicking on the “Date Equation” link will bring up a reference to all the adjustment tokens and alignment tokens that can be used, e.g: