Calculate working age by formula

1. Usage scenarios

With this example, let's learn how to calculate the length of service through the YIDA formula.

2. Implement functions

2.1 Configuration Table page

2.2 configuration field Formula

Modify the fields as needed.

(1) convert years by day

{"text":"INT(DAYS(DATE(​结束时间​),DATE(​开始时间​))/365)","marks":[{"from":{"line":0,"ch":14,"sticky":null},"to":{"line":0,"ch":20,"sticky":null},"value":"dateField_lc0b43vm","invalid":false},{"from":{"line":0,"ch":27,"sticky":null},"to":{"line":0,"ch":33,"sticky":null},"value":"dateField_lc0b43vl","invalid":false}],"isCmData":true}

(2) convert years by timestamp

{"text":"IF(OR(ISEMPTY(​开始时间​),ISEMPTY(​结束时间​)),\"\",INT((​结束时间​-​开始时间​)/1000/60/60/24/365))","marks":[{"from":{"line":0,"ch":14,"sticky":null},"to":{"line":0,"ch":20,"sticky":null},"value":"dateField_lc0b43vl","invalid":false},{"from":{"line":0,"ch":30,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"dateField_lc0b43vm","invalid":false},{"from":{"line":0,"ch":47,"sticky":null},"to":{"line":0,"ch":53,"sticky":null},"value":"dateField_lc0b43vm","invalid":false},{"from":{"line":0,"ch":54,"sticky":null},"to":{"line":0,"ch":60,"sticky":null},"value":"dateField_lc0b43vl","invalid":false}],"isCmData":true}

(3) accurate calculation

{"text":"IF(OR(ISEMPTY(​开始时间​),ISEMPTY(​结束时间​)),\"\",YEAR(DATE(YEAR(DATE(​结束时间​))-YEAR(DATE(​开始时间​))+100,MONTH(DATE(​结束时间​))-MONTH(DATE(​开始时间​))+1,DAY(DATE(​结束时间​))-DAY(DATE(​开始时间​))+2))-100)","marks":[{"from":{"line":0,"ch":14,"sticky":null},"to":{"line":0,"ch":20,"sticky":null},"value":"dateField_lc0b43vl","invalid":false},{"from":{"line":0,"ch":30,"sticky":null},"to":{"line":0,"ch":36,"sticky":null},"value":"dateField_lc0b43vm","invalid":false},{"from":{"line":0,"ch":62,"sticky":null},"to":{"line":0,"ch":68,"sticky":null},"value":"dateField_lc0b43vm","invalid":false},{"from":{"line":0,"ch":81,"sticky":null},"to":{"line":0,"ch":87,"sticky":null},"value":"dateField_lc0b43vl","invalid":false},{"from":{"line":0,"ch":105,"sticky":null},"to":{"line":0,"ch":111,"sticky":null},"value":"dateField_lc0b43vm","invalid":false},{"from":{"line":0,"ch":125,"sticky":null},"to":{"line":0,"ch":131,"sticky":null},"value":"dateField_lc0b43vl","invalid":false},{"from":{"line":0,"ch":145,"sticky":null},"to":{"line":0,"ch":151,"sticky":null},"value":"dateField_lc0b43vm","invalid":false},{"from":{"line":0,"ch":163,"sticky":null},"to":{"line":0,"ch":169,"sticky":null},"value":"dateField_lc0b43vl","invalid":false}],"isCmData":true}

3. Achieve results

4. Try it online

示例中心 | 开发者赋能平台

AI智能体时代，全新宜搭。业务创新，一搭就行。

https://www.aliwork.com/o/coc?tplUuid=TPL_LDOYHHBE8V9ZWLXM758C&from=yuque_subject