DATEDIF Function: Difference between revisions
No edit summary |
No edit summary |
||
| (6 intermediate revisions by 2 users not shown) | |||
| Line 7: | Line 7: | ||
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". | 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 == | == Format == | ||
'''DATEDIF(start_date; end_date; "unit")''' | '''DATEDIF(start_date; end_date; "unit")''' | ||
'''Date format:''' | '''Date format:''' | ||
| Line 20: | Line 18: | ||
*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. | *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:''' | '''Units:''' | ||
*'''d''' — difference in days. | *'''d''' — difference in days. | ||
Example 1: start_date 25.02.2007, end_date 26.02.2007 Result: 1 (day) | 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 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 | 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. | *'''m''' — difference in full months. | ||
| Line 42: | Line 43: | ||
Example 6: start_date 31.03.2007, end_date 30.04.2007 Result: 0 | Example 6: start_date 31.03.2007, end_date 30.04.2007 Result: 0 | ||
*'''y''' — difference in full years. | *'''y''' — difference in full years. | ||
| Line 48: | Line 50: | ||
Example 2: start_date 01.04.2007, end_date 01.03.2009 Result: 1 (year) | Example 2: start_date 01.04.2007, end_date 01.03.2009 Result: 1 (year) | ||
*'''ym''' — the difference in full months without considering years. | *'''ym''' — the difference in full months without considering years. | ||
| Line 54: | Line 57: | ||
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. | 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. | *'''md''' — the difference in days without considering months and years. | ||
| Line 60: | Line 64: | ||
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). | 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. | *'''yd''' — the difference in days without considering years. | ||
Example 1: start_date 01.01.2007, end_date 31.12.2007 Result: 364 (days) | 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 2: start_date 01.01.2008, end_date 31.12.2008 Result: 365 (days), because 2008 is a leap year | ||
== Example == | == Example == | ||
DATEDIF(<nowiki>{{Task. | DATEDIF(<nowiki>{{Task.Creation Date}}; {{Task.Legal Report Submission Date}}</nowiki>; "d") | ||
Latest revision as of 13:40, 4 December 2024
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")