How To Calculate Date Ranges In Reports And Letters
Date Range gives you fine control over the dates being scanned.
This topic shows you the Date Codes you can use to calculate
date ranges in your reports and letters.
The following entries are permitted for Date Range calculations:
@d | | Today's Date |
|
@w | |
First day of This Week (Sunday) |
|
@m | |
First day of This Month |
|
@y | |
First day of This Year |
These can also be modified with arithmetic operators as follows:
@d-1 | | Yesterday |
|
@w-1 | | First day of Last Week |
|
@m-1 | | First day of Last Month |
|
@y-1 | | First day of Last Year |
Finer control can also be obtained with more arithmetic operators:
@d-1 | | Yesterday |
|
@w-1+1 | |
First day of Last Week plus One Day |
|
@m-1+1 | |
First day of Last Month plus One Day |
|
@y-1+1 | |
First day of Last Year plus One Day |
Explanations
@w-1
Since @w represents the first day of the current week, this
statement represents the first day of this week minus one week.
The first day of a week is considered Sunday, so this code
does not mean this week's Sunday, but the Sunday of the previous
week.
@w-1+1
This is actually one day later, or the Monday of last week.
OTHER DATE CODES CAN BE USED IN THE SAME MANNER:
@m-6
This is interpreted as the first day of the month 6 months ago.
@m+1-1
This means the last day of this month (1st day of next month minus
one day).
@m-1d
This is the last day of last month. Notice how the 'd' tells the
date system you want to subtract days, not months. So if the
current month is February, this code is interpreted as
January 31st. However, if it is currently March, the system
interprets this code as February 28th, or 29th in a leap year.
@m-2
This is the first day of the month two months ago.
Remember that the standard date formats described
in the Help topic " How To Format Date Fields and Date Variables"
can be applied to any of your Date Range coding.
Date Math Table
@d+n | | Current day plus n days |
@d-n | | Current day minus n days |
|
@w+n+/-nd | |
Current week, starting on Sunday, plus n weeks +/- n days |
@w-n+/-nd | |
Current week, starting on Sunday, minus n weeks +/- n days |
|
@w+0+nd | |
Current week, starting on Sunday, plus n days |
@w-0-nd | |
Current week, starting on Sunday, minus n days |
|
@m+n+/-nd | |
Current month, starting on the 1st, plus months +/- n days |
@m-n+/-nd | |
Current month, starting on the 1st, minus n months +/- n days |
|
@m+0+nd | |
Current month, starting on the 1st, plus n days |
@m-0-nd | |
Current month, starting on the 1st, minus n days |
|
@y-n+/-nd | |
Current year starting on Jan 1, minus n years +/- n days |
@y+n+/-nd | |
Current year starting on Jan 1, plus n years +/- n days |
|
@y+0+nd | |
Current year starting on Jan 1, plus n days |
@y-0-nd | |
Current year starting on Jan 1, minus n days |
Output for 07/25/2009
@d+n @d-n | |
Current day plus/minus 1 day |
@d+4 | 07/29/2009 |
|
@w+n+/-nd @w-n+/-nd | |
Current week, starting on Sunday, +/- n weeks +/- n days |
@w+3+2d | 08/14/2009 |
|
@w+/-nd | |
Current week, starting on Sunday, +/- n days |
@w+3d | 07/25/2009 |
|
@m+n+/-nd @m-n+/-nd | |
Current month, starting on the 1st, +/- n months +/- n days |
@m+3+2d | 10/03/2009 |
|
@m+/-nd | |
Current month, starting on the 1st, +/- n days |
@m+3d | 07/04/2009 |
|
@y-n+/-nd @y+n+/-nd | |
Current year starting on Jan 1, +/- n years +/- n days |
@y+4+10d | 01/11/2013 |
|
@y+/-nd | |
Current year starting on Jan 1, +/- n days |
@y-4d | 12/28/20086 |
With Variables
@var1! = @d+4
@var2! = @w+3+4d
@var3! = @w+3d
var1: @var1
var2: @var2
var3: @var3
var1: 07/29/09
var2: 08/16/09
var3: 07/25/09
Adding And Subtracting Dates
Dates can be added or subtracted using Arithmetic operators.
When you add or subtract dates, the result will be displayed
as a number formatted as a simple integer. This applies to
any date math, even different months and years can be used.
This could be used to determine days that have lapsed
since a payment for instance, or for interest calculations.
Example 1:
This example creates an integer variable to hold the value
of the number of days between the debtor's Listed and
Charged dates.
@varDate# = 0
@varDate = @(de.li-de.ch)
This is the number of days = @varDate
Output:
This is the number of days = 168
Example 2:
Where the debtor's Listed Date is 03/17/09 and the debtor's
Payment Date is 08/07/09, the following are the results
of date math.
1. Subtracting the Payment Date from the Listed Date
This is de.pay - de.li = @(de.pay-de.li)
Output:
This is de.pay - de.li = 143
2. Using variables to subtract the Payment Date from the
Listed Date
@varDate = @de.pay
@varBDate = @de.li
This example uses variables = @(varDate-varBDate)
Output:
This example uses variables = 143
3. Adding a number of days to the Listed Date
This is de.li + 143d = @de.li+143d
Output:
This is de.li + 143d = 08/07/09
4. Subtracting a number of days from the Payment Date
This is de.pay - 143d = @de.pay-143d
Output:
This is de.pay - 143d = 03/17/09
Dates are also used in advanced report design.
You will find references to them in How-to topics covering Lists
and Loops, Conditionals, Variables and Math topics. Remember
to review the list of pre-defined reports for examples. Also,
Collect! Technical Services can build custom reports for complex
use of date ranges and pulling information for reports.
See Also
- How To Format Date Fields and Date Variables
- Report Sample to view sample reports and letters
- Report Topics Index for a list of all report and letter topics
|
Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.
Please click this link to send us your comments: helpinfo@collect.org