Using DATEDIF to Calculate the Difference Between Two Dates
Introduction
When using Jodoo in business scenarios, sometimes you need to calculate the difference between two given dates.
For example, in project management, you have a project information form and many project progress forms of different stages. To calculate time spent in different stages, you can link these forms in data factory, and gather the start date and end date of each stage with the Join node. Then you can use the function to calculate the time used for each stage of the project, which helps evaluate work efficiency.
The DAYS function can only calculate the number of days between two dates. However, sometimes you need to calculate precisely the number of hours or minutes between two dates.
Therefore, you can use the DATEDIF function to calculate the difference between two date values based on a specified interval.
Using the Function
DATEDIF(start_date, end_date, [unit])
The DATEDIF function has the following arguments:
- start_date: Required. A date that represents the first or starting date of a given period.
- end_date: Required. A date that represents the last or ending date of the period.
- Unit: Optional. An optional argument, the default value of Unit is "d". It can be set as "y", "M", "d", "h", "m", and "s".
Unit | Return Value |
“y” | The number of complete years in the period. |
“M” | The number of complete months in the period. |
“d” | The number of complete days in the period. |
“h” | The number of complete hours in the period. |
“m” | The number of complete minutes in the period. |
Note:
The argument is case-sensitive. Uppercase M stands for month and lowercase m stands for minute.
The DATEDIF function can calculate the difference between two date values in the format of years ("y"), months ("M"), days ("d"), hours ("h" ), minutes ("m"), and seconds ("s").
1. Calculate the number of years between two dates.
2. Calculate the number of months between two dates.
3. Calculate the number of days between two dates.
4. Calculate the number of hours between two dates.
5. Calculate the number of minutes between two dates.
6. Calculate the number of seconds between two dates.
7. Use the DATEDIF function in data factory.
Go to Data Factory > Field Settings > Calculated Fields.
Note:
If the start date is greater than the end date, the DATEDIF function cannot finish the calculation.