Re: [算表] 如何計算員工的年資... --日期,DATEDIF

看板Office作者JieJuen (David)時間17年前 (2008/11/26 09:14)推噓1(1推 0噓 0→)

留言1則, 1人參與討論串4/6 (看更多)

站在巨人的肩膀可以看得更遠 by 牛頓 ! XD 沒想到這種麻煩的算式也可以有人討論~ ^^ 我試了一下您的算式(DATE) 結果有蠻多地方不一樣 (見後表) 有很多零碎的問題是規則不清楚或理解沒有相同導致若能在關鍵的一些日期寫好需要的答案比較清楚不過關鍵的日期好像很多 ... ※ 引述《mddc62 (我是大豬頭)》之銘言： : 分享我的作法... : 先算出年，再算出月，再算出日 : 1.先算出年 : year(b1)-year(a1) : 2.月日要一起看 : if(date(year(b1),month(a1),day(a1))<=b1, : if(date(year(b1),month(a1)+6,day(a1)-1)>=b1,0.5,1), : if(date(year(b1)-1,month(a1)+6,day(a1)-1)>=b1,0,-0.5)) : 把1+2=X : ------------------------------------------------------------------ : 參考版主大人可以修改成為：過抬了~~^^"" : (果然站在巨人的肩膀可以看得更遠) 我剛好有留一點伏筆沒想到可以用到 XD 兩條式子第一條比較短為何還要第二條呢? 除了思路不同外，公式分開寫時第二條反而比較好寫因為有相同的部分 : x= roundup((datedif(a1,b2,"m")/12)*2,0)/2 /6 (?) y =ROUNDUP(DATEDIF(A2,B2,"m")/6,0) 意義即計算過了幾個半年 : 整個公式可以更簡化 : ------------------------------------------------------------------ : 剩下就這樣囉 : Min(45,min(x*2,30)+max((x-15),0)) MIN(y-IF(y>30,CEILING(y/2,1)-15),45) →在本題沒問題但ceiling這樣改不保險 MIN(IF(y>30,INT(y/2)+15,y),45) 長度都差不多不過算法差不少多用幾次min max的方法也不賴我也有試過一些其他巧妙加減的方法但是總是會有東西要修所以如果能照定義的描述"直譯" 大概比較安心但目前這定義或許有歧義像看到推文才發現您把基數視為可切割的所以這類日期問題麻煩很大的一個原因就是定義不清楚即使知道excel怎麼寫還是會不一樣真麻煩呀囧.. 人資辛苦了 XD : 上面的日期要再對一下+-1的問題(滿月的定義) : 其餘應該有解決問題.... 嗯下面文章試著改了一下 : ※ 引述《bluekai (火考三吃)》之銘言： : : 軟體: excel : : 版本: 2003 or 2007 : : 我有以下的資訊和限制，需要計算出退休員工可以領多少退休金， : : 而退休金中很重要的就是年資的計算.. : : 擁有資訊：到職日、離職日 : : 法令限制： : : 1. 15 年以下部分，每年 2 個基數 : : 2. 超過 15 年部分，每一年 1 個基數，最高為 45 個基數 : : 3. 超過半年未滿一年以一年計算，未滿半年以半年計算。 : : 假設欄位如下： : : A B C D : : 1 到職日期離職日期年資基數 : : 2 1998/06/18 2008/11/17 : : C2 的部分我是以 datedif 函數來計算出在公司 X年X月X日 : : 但是 D2 是要用 if 來寫還是哪個函數？那又該如何寫，我就沒概念.. : : 來跟版上大大們求救... 一些相同與不同的地方基數始終年月日 DATEIF DATE 2000/1/1 2000/1/1 0 0 0 0 1 2000/1/1 2000/1/2 0 0 1 0 1 2000/1/1 2000/1/31 0 0 30 0 1 2000/1/1 2000/2/1 0 1 0 1 1 2000/1/1 2000/7/1 0 6 0 1 2 2000/1/1 2000/8/1 0 7 0 2 2 2000/1/1 2000/12/31 0 11 30 2 2 2000/1/1 2001/1/1 1 0 0 2 3 2000/1/1 2001/2/1 1 1 0 3 3 2000/1/1 2014/12/31 14 11 30 30 30 2000/1/1 2015/1/1 15 0 0 30 30.5 2000/1/1 2015/6/30 15 5 29 30 30.5 2000/1/1 2015/7/1 15 6 0 30 31 2000/1/1 2015/8/1 15 7 0 31 31 2000/1/1 2029/1/1 29 0 0 44 44.5 2000/1/1 2029/7/1 29 6 0 44 45 2000/1/1 2029/8/1 29 7 0 45 45 寫"dateif" "date"只是標示式子用有需要dateif應該可以修改成date的結果(上表中) z =ROUNDUP(DATEDIF($A2,EDATE($B2,1),"m")/6,0) 或 ^^^^^^^^^^^^ =ROUNDUP(DATEDIF($A2,DATE(YEAR($B2),MONTH($B2)+1,DAY($B2)),"m")/6,0) ^^^^^^^^^^^^ 月底會不一樣基數例 2000/1/30 ~ 2000/1/31 =MIN(IF(z>30,z/2+15,z),45) 式子太多又比較又更改有誤請見諒:~~ 關鍵日不只上面那些 (問題還沒完...) 2000/2/29 2000/12/31 0 10 2 2 2 2000/2/29 2001/1/1 0 10 3 2 1 2000/2/29 2001/2/28 0 11 30 2 1 2000/2/29 2001/3/1 1 0 0 2 3 (待續?) ※ 編輯: JieJuen 來自: 218.164.50.5 (11/26 12:41)