Solution Overview
The following solutions can all be used to perform date addition and subtraction calculations. However, their calculation ranges and best-fit scenarios vary. See the table below for details:
Solution | DATEDELTA() Function | DATE() Function | Timestamp Calculation |
Calculation Unit | Day | Year, month, day, hour, minute, second | Year, month, day, hour, minute, second |
Calculation Logic | Directly add or subtract days based on the specified date. | Convert the year, month, and day, or year, month, day, hour, minute, and second, into a date object first, and then add or subtract the corresponding parameter. | Directly add or subtract values from the Date&Time field. The time unit used in the calculation is milliseconds. |
Calculation Example | For example, 10 days later: | For example, 10 years later: | For example, 10 hours later: |
| — | Other units can be calculated in the same way. | Other units can be calculated in the same way. |
Best-fit Scenario | Adding or subtracting days. | Adding or subtracting years or months. | Adding or subtracting hours, minutes, or seconds. |
DATEDELTA() Function
In a product warehousing scenario, you can automatically calculate the expiration date of a product based on its production date and shelf life.
Go to the Product Expiration Time Calculation form, select the Expiration Time field, and set the Formula to: DATEDELTA(Production Date,Shelf Life)
This formula adds the shelf life to the production date to calculate the expiration time of the product. After completing the settings, click OK.
DATE() Function
In an employee management scenario, you can calculate an employee’s regularization date based on the employee’s entry date and probation period.
In an equipment maintenance scenario, you can calculate the next maintenance date based on the equipment’s last maintenance date and maintenance cycle.
Go to the Employee Regularization Date Calculation form, select the Confirmation Date field, and set the Formula to: DATE(YEAR(DATE(Start Date)),MONTH(DATE(Start Date))+Probation Period,DAY(DATE(Start Date)))
After completing the settings, click OK. The formula is explained below:
Formula | Description |
| Converts the entry date into a date object in the year-month-day format, making it easier to add or subtract the month parameter. |
| Adds the probation period, in months, to the month parameter to calculate the regularization date. |
Hour, Minute, and Second Calculation Scenario
You can also use the DATE() function to add or subtract dates by hour, minute, or second.
Select the 10 Hours Later field and set the Formula to:
DATE(YEAR(DATE(Current Time)),MONTH(DATE(Current Time)),DAY(DATE(Current Time)),HOUR(DATE(Current Time))+10,MINUTE(DATE(Current Time)),SECOND(DATE(Current Time)))
After completing the settings, click OK. The formula is explained below:
Formula | Description |
| Converts the current date and time into a date object in the year-month-day-hour-minute-second format, making it easier to add or subtract the hour parameter. |
| Adds 10 to the hour parameter to calculate the date and time 10 hours later. |
Effect
Timestamp Calculation
The data type of a Date&Time field is a timestamp, which is an integer value measured in milliseconds. You can directly reference the Date&Time field, add or subtract a time value in milliseconds, and return the result to a new Date&Time field.
For example,in a vehicle rental management scenario, you can calculate the vehicle’s due return time based on the rental start time and rental duration. This helps with vehicle scheduling and management.
Go to the Vehicle Due Return Time Calculation form, select the Vehicle Due Return Time field, and set the Formula to: Rental Start Time+Rental Duration*60*60*1000
This formula converts the rental duration into milliseconds and directly adds it to the rental start time to calculate the due return time. After completing the settings, click OK.
