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:

These can also be modified with arithmetic operators as follows:

Finer control can also be obtained with more arithmetic operators:

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

Output for 07/25/2009

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