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: