Date Calculations

Date calculations are a type of calculation that either:

  • use a date as an input,
  • return a date as an output, and/or
  • do both.

Rulestar supports a range of date calculations using our custom calculation engine.

There are three main types of date calculation:

  1. Durations - calculate the duration (e.g. the number of months) between two dates.
  2. Comparisons - comparing the order of two dates (e.g. which one is first).
  3. Manipulation - adding or subtracting a duration of time (e.g. 5 days) to a date.

The type of value that a date calculation returns determines what type of field is required to perform the calculation. If a date calculation returns a number (e.g. a duration), the calculation must be done in a number field. If a date calculation returns a date or time (e.g. a manipulation), a Date/Time Field must be used.

 

Source of dates

There are two sources of dates that can be used as inputs to a date calculation:

  1. Date/Time Fields  - an answered Date/Time Field (including a Date/Time Field answer from a calculation); and
  2. Today - equals the date on which a user is answring the smart online form. This is case sensitive as must be written as "Today".

In the following examples, a reference to Date (including Date1 and Date2) is any date provided in one of these formats and X is any number.

 

Number Fields

Use a number field for the following types of date calculations:

  1. Durations - the duration of time (e.g. the number of days) between two dates.
  2. Boolean date comparison - a boolean (true or false) comparison on the order of two dates.
  3. Date properties - the day, month or year of the date.

These calculations are performed in number fields as their return value is a number (not a date).

To perform a date calculation within a number field, you will first need to create a new number field (shown here). As with all calculations, ensure that the field is either read-only or hidden.

 

Durations

Date duration calculations to return the duration of time between two dates are done with the "Diff" methods. The duration can be returned as a number of years, months or days. For example, to calculate the number of years between two dates, you can use the following "DIffYears" method:

Date1.DiffYears(Date2)

If you wanted to know the number of months between two dates, you would use:

Date1.DiffMonths(Date2)

For the number of days between two dates, you can use:

Date1.DiffDays(Date2)

 

If Date1 is after Date2, the result will be a positive number. If Date1 is before Date2 , the result will be a negative number.

 

A fourth date duration method can be used when the unit (years, months and days) is variable within your smart online form. This unit can be changed given the user's answers eg, to a radio or select field.

To perform a date duration calculation using the "Diff" method:

Date1.Diff(Date2, "unit")

Valid units of the "Diff" method are as follows and are not case sensitive:

  • year or years,
  • month or months, and
  • day or days.

Results of all "Diff" methods are truncated to zero decimal places, returning an integer eg, 0.95 years returns 0.

Boolean date comparisons

A boolean date comparison compares two dates and returns either 0 or 1, representing the false and true boolean values. Boolean date comparisons are performed using the same equality and relational operators that can be used in logical expressions within the logic builder.

For example, to see Date1 is strictly earlier than Date2, you would use the following calculation:

Date1 < Date2

If Date1 is the earlier date this calculation will return 1, otherwise, it will return 0.

To check Date1 is the same as Date2, you would use the equality operator:

Date1 = Date2

If the dates are the same, this calculation will return 1, otherwise, it will return 0.

Date properties

You can use number field calculation to access the numeric value of the day, month or year of a date.

For example, the calculation to return the "month" of a date is:

Date.Month

Accessing the year and day properties follow as expected.

For example, if a Date/Time Field is answered 12 May 2019:

  1. Date.Day will return 12, and
  2. Date.Year will return 2019.

There is a fourth date property "MonthName" that returns the name of the month e.g. "April". Perform this calculation within a Text Field.

 

Date/Time Fields

As Date/Time Fields can only hold and display dates and times, any calculation performed in a Date/Time Field must return a date, a time, or a date and time. Date/Time Fields are therefore used to perform the following calculations:

  1. Manipulations - add or subtract a period of time (e.g. 5 months) to a date.
  2. Min/max date - find the earliest or latest of two or more dates.

 

Manipulations

Date manipulation calculations to return another date are done with the "Add" methods. The units of time that can be added to or subtracted from a date are years, months and days. For example, to calculate the date one year into the future from a date, you can use the following "AddYears" method:

Date.AddYears(X)

The "Add" methods accept negative values to perform date subtractions. For example, to calculate the date five months before a date, you would use the "AddMonths" method with a negative X:

Date.AddMonths(-X)

In addition to the "AddDays" method that follows the same syntax, the following date manipulation calculations are equivalent:

Date.AddDays(X) is the same as Date + X

Date.AddDays(-X) is the same as Date - X

Like the "Diff" method, there is a generic "Add'" method that can be used when the unit (years, months, days) is variable within your smart online form. Using the "Add" method is easy: 

Date.Add(X, "unit")

Valid units of the "Diff" method are:

  • year or years,
  • month or months, and
  • day or days.

Units are not case-sensitive.

 

Minimum and maximum date comparisons

Unlike the boolean date comparisons that return the true and false values (0 and 1), the minimum and maximum date comparisons return the minimum or maximum of the dates compared. You can compare two or more dates.

For example, to find out which is the earliest of Date1, Date2 and Date3 you would use the following calculation:

Min(Date1,Date2,Date3)

Likewise, you can use Max() to find out which is the latest of two or more dates.