Term Function
Description:
This function calculates the years, months and days between two dates and includes both the start date and end date in the result.
Since the number of days varies for different calendar months, there is no perfect way of calculating term and the results may vary depending on the approach used. The Term function supports two ways of calculating the months:
- Where the term includes a full calendar month, the count of calendar months is calculated before the count of days (default, parameter set to "true");
- Where the term starts mid-month, the month is calculated from the start date and the length of the month is determined by the month of the start date (parameter set to "false").
In most cases, both approaches will use the same logic and produce the same result. However, there are cases where the results will differ. To illustrate the difference in these two approaches, please see the following examples.
From Date | To Date | Parameter "True" (Default) | Parameter "False" |
|---|---|---|---|
August 20 | September 29 | Result: 1 month and 10 days | Result: 1 month and 10 days |
August 20 | September 30 | Result: 1 month and 12 days Calculation: September 1-30 = 1 month (30 days) August 20-31 = 12 days | Result: 1 month and 11 days Calculation: August 20 - September 19 = 1 month (31 days) September 20-30 = 11 days |
August 31 | September 30 | Result: 1 month and 1 day | Result: 1 month and 1 day |
August 30 | September 30 | Result: 1 month and 2 days Calculation: September 1-30 = 1 month (30 days) August 30-31 = 2 days | Result: 1 month and 1 day Calculation: August 30 - September 29 = 1 month (31 days) September 30 = 1 day |
August 31 | October 15 | Result: 1 month and 16 days | Result: 1 month and 16 days |
August 30 | October 15 | Result: 1 month and 17 days Calculation: September 1-30 = 1 month (30 days) August 30-31 = 2 days October 1-15 = 15 days | Result: 1 month and 16 days Calculation: August 30 - September 29 = 1 month (31 days) September 30 = 1 day October 1-15 = 15 days |
Returns:
Text*
Parameters:
Parameter | Data Type | Description |
|---|---|---|
1 | Date | First day of the term |
2 | Date | Last day of the term |
3 | Text | Format of the resulting term: "ymd" = years, months, days "md" = months, days "wd" = weeks, days "d" = days "YMD" = years, months, days, in words "MD" = months, days, in words "WD" = weeks, days, in words "D" = days, in words |
4 | Boolean | "True" to count full calendar months before counding the days (Default) "False" to count the months from the start date where the term starts mid-month |
Examples:
When using functions inside a field, remember the field brackets:
{Term(DateFrom, DateTo, “ymd”)}
The examples below use the following dates for the calculation:
DateFrom: August 20, 2020
DateTo: September 29, 2021
Expression | Result |
|---|---|
{Term( DateFrom, DateTo, "ymd" )} | 1 year, 1 month, 10 days |
{Term( DateFrom, DateTo, "YMD", false )} | one year, one month, ten days |
{Term( DateFrom, DateTo, "md", true )} | 13 months, 10 days |
{Term( DateFrom, DateTo, "wd" )} | 58 weeks |
{Term( DateFrom, DateTo, "D" )} | four hundred and six days |
To change the separators between the years, months and days, you can specify the format for this function.
For example: {Term( DateFrom, DateTo, "ymd" ) format ", | and |."}
Related Content
-
Date Functions
Format:
Article,
1 min read