DATEDIF Function
The function DATEDIF calculates the number of days, months, or years between two dates. The start date must be earlier than or equal to the end date. Otherwise, the function returns "ERROR".
Format
DATEDIF(start_date; end_date; "unit")
Date format:
- The date is written strictly in the order day-month-year;
- Day and month can be written with or without a leading zero, for example, 01.01.2000, 15.10.2000, 1.1.2000;
- The year can be written in 4 digits (01.01.2015) or in 2 (01.01.15) (a year written in two digits is interpreted as the year of the current century - 08.12.93 = 08.12.2093, 08.12.00 = 08.12.2000);
- Any symbol (except a digit) can be a separator in the date. If the separator is a period (15.10.2020), the date is not enclosed in quotation marks, and if / or - (15/10/2020 or 15-10-2020) — then only in quotation marks.
Units:
- d — difference in days.
Example 1: start_date 25.02.2007, end_date 26.02.2007 Result: 1 (day)
Example 2: start_date 01.02.2007, end_date 01.03.2007 Result: 28 (days)
Example 3: start_date 28.02.2008, end_date 01.03.2008 Result: 2 (days), because 2008 is a leap year
- m — difference in full months.
Example 1: start_date 01.02.2007, end_date 01.03.2007 Result: 1 (month)
Example 2: start_date 01.03.2007, end_date 31.03.2007 Result: 0
Example 3: start_date 01.02.2007, end_date 01.03.2009 Result: 25 months
Example 4: start_date 31.03.2007, end_date 01.05.2007 Result: 1 (month)
Example 5: start_date 01.04.2007, end_date 01.05.2007 Result: 1 (month)
Example 6: start_date 31.03.2007, end_date 30.04.2007 Result: 0
- y — difference in full years.
Example 1: start_date 01.02.2007, end_date 01.03.2009 Result: 2 (years)
Example 2: start_date 01.04.2007, end_date 01.03.2009 Result: 1 (year)
- ym — the difference in full months without considering years.
Example 1: start_date 01.02.2007, end_date 01.03.2009 Result: 1 (month) — the end date 01.03.2009 and the modified start date 01.02. 2009 (the year of the start date is replaced by the year of the end date because 01.02 is less than 01.03) are compared.
Example 2: start_date 01.04.2007, end_date 01.03.2009 Result: 11 (months) — the end date 01.03.2009 and the modified start date 01.04. 2008 (the year of the start date is replaced by the year of the end date minus one year because 01.04 is more than 01.03) are compared.
- md — the difference in days without considering months and years.
The start date is aligned to the end date for comparison.
Example 1: start_date 31.08.2007, end_date 01.05.2008 — the start date is transformed into 31.04.2008. But April has 30 days, so April 31st will be adjusted and become April 30th. The same works with February, including leap years (i.e., there will be no non-existent February dates. Instead, they will be replaced by the last February date for the year of the end date).
- yd — the difference in days without considering years.
Example 1: start_date 01.01.2007, end_date 31.12.2007 Result: 364 (days)
Example 2: start_date 01.01.2008, end_date 31.12.2008 Result: 365 (days), because 2008 is a leap year
Example
DATEDIF({{Task.Creation Date}}; {{Task.Legal Report Submission Date}}; "d")