Related Information Examples & Tutorials

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.

tip.gif 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.

Top of page.

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+2d08/14/2009
 
@w+/-nd  Current week, starting on Sunday, +/- n days
@w+3d07/25/2009
 
@m+n+/-nd
@m-n+/-nd
  Current month, starting on the 1st, +/- n months +/- n days
@m+3+2d10/03/2009
 
@m+/-nd  Current month, starting on the 1st, +/- n days
@m+3d07/04/2009
 
@y-n+/-nd
@y+n+/-nd
  Current year starting on Jan 1, +/- n years +/- n days
@y+4+10d01/11/2013
 
@y+/-nd  Current year starting on Jan 1, +/- n days
@y-4d12/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

Top of page.

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

tip.gif 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.

Top of page.

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

Top of page.

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