式による勤続年数の計算

1. 使用シーン

この例を利用して、私たちはどのように適切な公式で勤続年数を計算するかを学びます。

2.機能を実現する

2.1構成表ページ

2.2フィールド式の設定

実際のニーズに合わせてフィールドを変更することに注意してください。

(1) 日換算年

{"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) タイムスタンプで年を換算する

{"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) 精密計算

{"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}