With this function, you can do two kinds of data calculations:
- Calculate the number of hours, days, or other time unit between two different input dates.
- Calculate a new date by adding a given number of hours, days, or other time unit to a given date.
Inputs
To calculate the difference between two dates, the input fields should be two dates in the same format.
To calculate a new date, the first input is the starting date. The second input is a positive or negative number of units (e.g. days, hours) to be added to the date. The result is the new date in the same format as the start date.
When calculating a date in the future, a plus sign (+) must precede the input number. When calculating a date in the past, a minus sign (-) must precede the input number (see screenshot below).
Source format
Defines the date format of the source data, such as yyyy.MM.dd or dd MMM yyyy HH:mm:ss See Date formats below for details.
Unit
Defines the time unit for the date calculation. Possible values include seconds, minutes, hours, days, weeks, months, and years.
Time zone
Defines the time zone for date functions, taking daylight saving into consideration. For example, you can calculate the difference between two dates in locations with and without daylight saving (i.e., Eastern Savings Time and India Time) and the result will factor the difference in the number of days.
If no time zone is specified, then the system time zone is used.
Language
Sets the input and output language.
The language should be set as a combination of a standard language code and of a standard country code. They can be found at http://www.loc.gov/standards/iso639-2/php/English_list.php and at https://en.wikipedia.org/wiki/ISO_3166-2. The pattern is <language>_<country>, also possible is only <language>. For example, English can be set as en_US, en_UK or en_AU, and German can be set as de_DE, de_CH or de_AT.
If <country> or both codes are not set, for the missing code will be set the corresponding system default value.
Examples
| Input1 | Input2 | Source format | Unit | Expected Result |
|---|---|---|---|---|
| 2022-01-15 | +10 | yyyy-MM-dd | days | 2022-01-25 |
| 2022-01-15 | -10 | yyyy-MM-dd | days | 2022-01-05 |
| 2022-01-15 22 | +4 | yyyy-MM-dd H | hours | 2022-01-16 02 |
| 2022-01-15 22 | -4 | yyyy-MM-dd H | hours | 2022-01-15 18 |
| 2022-01 | +3 | yyyy-MM | months | 2022-04 |
| 2022-01 | -3 | yyyy-MM | months | 2021-10 |
| 2022 | +1 | YYYY | years | 2023 |
| 2012 | -1 | YYYY | years | 2011 |
| 22 | +5 | YY | years | 27 |
| 22 | -5 | YY | years | 17 |
| 09 | 3 | MM | months | 12 |
| 09 | -3 | MM | months | 06 |
| Jan | +3 | MMM | months | Apr |
| Jan | -3 | MMM | months | Oct |
| 22 | +4 | HH | hours | 02 (next day) |
| 22 | -4 | HH | hours | 18 |
| 31.12.1899 | +40909 | days | 01.01.2012 | |
| 31.12.1899 | 01.01.2012 | days | 40909 | |
|
10.04.2025 |
|
|
|
|
The last two samples can be used for the conversion of a date to a timestamp in Excel.
Date formats
The function DateDuration recognizes the letters of the standard SimpleDateFormat, which can be found under
http://download.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html (not all are useful for this function).
The letters q, Q, and v are Jedox Integrator-specific enhancements of the standard SimpleDateFormat.
Below is an overview of the most used letters for the function DateDuration:
|
Letter |
Meaning |
Result for 31.12.2014 15:35:20 |
|
d |
Day in month |
31 |
|
M |
Month in year |
MM: 12, MMM: Dec, MMMM: December |
|
y |
Year |
yyyy: 2014, yy: 14 |
|
H |
Hour in day (0-23) |
15 |
|
m |
Minute in hour |
35 |
|
s |
Second in minute |
20 |
Updated July 17, 2026