Date automatically plus ten days
How to automatically calculate a date plus a specified number of days, such as production date + shelf life, and automatically calculate the Expiration Time.
Form design component
1. DATEDELTA(date, deltadays)
DATEDELTA function adds or subtracts a specified date by a specified number of days, that is, production date + shelf life = expiration time.
DATEDELTA formula calculation
Effect
2. DATE(year,month,day)
Write a function in the DATE field, DATE (year, month, day), to return the corresponding DATE. Therefore, we can extract the year, month and day in the production date first, then the year and month in the expiration time remain unchanged, and the day in the expiration time = the day in the production date + the shelf life.
The function to extract the date of the year is as follows:YEAR(DATE (production DATE),MONTH(DATE (production DATE),DAY(DATE (production DATE)
DATE formula calculation
The function that calculates the expiration time is: DATE (year in the production DATE, month in the production DATE, day in the production DATE + shelf life). In the previous step, the method of extracting the year, month and day in the production date has been given, and it can be directly substituted into this formula.
The following code can be directly copied to the formula editor,Replace the production date and shelf life Fields.
{"text":"TIMESTAMP(DATE(YEAR(DATE(生产日期)),MONTH(DATE(生产日期)),DAY(DATE(生产日期))+保质期))","marks":[{"from":{"line":0,"ch":25,"sticky":null},"to":{"line":0,"ch":31,"sticky":null},"value":"dateField_ksj6bl0e","invalid":false},{"from":{"line":0,"ch":45,"sticky":null},"to":{"line":0,"ch":51,"sticky":null},"value":"dateField_ksj6bl0e","invalid":false},{"from":{"line":0,"ch":63,"sticky":null},"to":{"line":0,"ch":69,"sticky":null},"value":"dateField_ksj6bl0e","invalid":false},{"from":{"line":0,"ch":72,"sticky":null},"to":{"line":0,"ch":77,"sticky":null},"value":"numberField_ksj6bl0h","invalid":false}],"isCmData":true}
Effect
3. Timestamp calculation
Timestamp is a relatively simple and rough calculation method. The data type in the date field is a timestamp, which is an integer value in milliseconds. Then, we directly take the timestamp of the production date, plus the number of milliseconds of the shelf life, then directly return to the Expiration Time field.
The unit of shelf life is days, so it must be converted into milliseconds before calculation.
The following code can be directly copied to the formula editor,Replace the production date and shelf life Fields.
{"text":"IF(ISEMPTY(生产日期),\"\",生产日期+保质期*24*3600*1000)","marks":[{"from":{"line":0,"ch":11,"sticky":null},"to":{"line":0,"ch":17,"sticky":null},"value":"dateField_kssa5620","invalid":false},{"from":{"line":0,"ch":22,"sticky":null},"to":{"line":0,"ch":28,"sticky":null},"value":"dateField_kssa5620","invalid":false},{"from":{"line":0,"ch":29,"sticky":null},"to":{"line":0,"ch":34,"sticky":null},"value":"numberField_ksj6bl0h","invalid":false}],"isCmData":true}
Timestamp calculation
4. Method comparison
DATEDELTA(date, deltadays) is calculated in days.By dayFor calculation, it is most convenient to use DATEDELTA function.
DATE(year,month,day) isYear, month, dayIn units. If you also need to calculate the year or month, it is more convenient to use DATE(year,month,day). For example, if the expiration time of a passport is ten years, the expiration time formula is DATE(YEAR(DATE (passport effective DATE) +10,MONTH(DATE (passport effective DATE),DAY(DATE (passport effective DATE)-1).
Therefore, DATE(year,month,day,hour,minute,second) can also be derived for calculation with time minutes and seconds.
The timestamp is calculated in milliseconds and is applicable to all time calculations, because any time period needs to be added or subtracted can be converted into milliseconds for calculation. When the minimum unit of the time period to be added or minus containsMinutes and secondsWhen, it is more convenient to use timestamps to calculate.
YIDA in order to better optimize the content and quality of YIDA user manual, it takes you 3-5 minutes to fill in the document feedback questionnaire. The document feedback questionnaire is submitted anonymously, and the questionnaire information is only used for YIDA document experience feedback collection. Thank you for your support for YIDA!
-------------------- Get the latest information YIDA, welcome to follow US--------------------
