エクセルで複雑な退職金の計算をしたいのですが

質問

Excel2007で次の退職金の計算をしたいのですがご教授願います。

起算日　A1
退職日　A2
在職期間　=DATEDIF(A1,A2,"ｙ")&"年"&DATEDIF(A1,A2,"ｙｍ")&"ヶ月"&DATEDIF(A1,A2,"md")&"日"　　　　　　　←この計算式で○年○月○日を計算しました。

その後次の計算が必要になります。
(1)在職年数に端数がある時は月割りで計算し、1ヶ月未満の端数は1ヶ月に切り上げる。
　端数日数を1ヶ月単位に切り上げ、○年○ヶ月を求めます。
(2)在職年月に基本給を掛けて、更に係数を掛けて退職金を求める。
　（○年＋○ヶ月/12）×基本給×係数　となります。

そこで(1)をトライしてみましたが、(2)の方法がわからないので、(1)の検証もできません。
(1)の計算は次のように求めてみました。
　=DATEDIF(A1,A2,"ｙ")&"年"&DATEDIF(A1,A2,"ｙｍ")+(DATEDIF(A1,A2,"md")>0)&"ヶ月"
この計算では在職期間が○年11ヶ月○日となった場合、求めた在職年数は○年12ヶ月となってしまい、年に繰り上がりません。従って(2)の方法に影響が出そうですが、(2)の方法自体がわからないのでお手上げ状態です。

(1)と(2)、あるいは最初の方法からからでも構いませんので、ご教授を頂きたいと思います。

回答No.15

bunjii · ベストアンサー

度々の訂正で申し訳ありません。
回答No.14の訂正です。

誤　年数（整数） =DATEDIF(A1,A2,"ｙ")+INT(DATEDIF(A1,A2,"yｍ")+ROUNDUP(DATEDIF(A1,A2,"md")/100,0)/12)
正　年数（整数） =DATEDIF(A1,A2,"ｙ")+INT((DATEDIF(A1,A2,"yｍ")+ROUNDUP(DATEDIF(A1,A2,"md")/100,0))/12)

1組の括弧が抜けていました。

お礼

bunjiiさん有り難うございました。
年数、月数とも表示されました。
お手数をお掛けしました。

回答No.14

bunjii

回答No.11の追加です。

年数（整数） =DATEDIF(A1,A2,"ｙ")+INT(DATEDIF(A1,A2,"yｍ")+ROUNDUP(DATEDIF(A1,A2,"md")/100,0)/12)
月数（端数） =MOD(DATEDIF(A1,A2,"yｍ")+ROUNDUP(DATEDIF(A1,A2,"md")/100,0),12)
式中の ROUNDUP(DATEDIF(A1,A2,"md")/100,0)/12) で、/100は１ヶ月の最大日数（31）以上を除数にすれば論理的に合います。

入社日と退職日を何れか一方を組み入れるときはA1へ入社日を入力すれば良いでしょう。
入社日と退職日のの両方を組み入れるときはA1-1とするかA2+1とすれば良いでしょう。
A1から1日引くこととA2へ1日加えるのは結果が同じです。

回答No.13

kmetu

補足です

繰上げは
月数+(A1<>A2)

だとA2が2月だとおかしくなるので

月数+IF(EOMONTH(A2,0)<>A2,(DAY(A1)<>DAY(A2)),IF(MONTH(A2)=2,(DAY(A2)-DAY(A1)>0),IF(NOT(OR(MONTH(A2)=2,AND(EOMONTH(A1,0)=A1,EOMONTH(A2,0)=A2))),(DAY(A1)<>DAY(A2)))))

でいかがでしょう。

回答No.12

kmetu

先にお知らせしたようにDATEDIFにはバグが存在しますから、退職金を計算するような金銭計算に利用するのは止めたほうがいいのではないでしょうか。

たとえば

=DATEDIF(A1,A2,"YM")
で
A1に2001/1/31　　年は何年にしても同じです。
A2に2013/2/28
とすると結果は
0
になります。

この場合、通常在籍期間は12年1ヶ月と考えると思うのですが
DATEDIFを使うと0と結果がでて12年0ヶ月ですので在籍期間が1ヶ月短くなります。

また、先に示させていただいたページに書かれていましたが、法的には

◆第百四十条
日、週、月又は年によって期間を定めたときは、期間の初日は、算入しない。ただし、その期間が午前零時から始まるときは、この限りでない。

ですので、在籍期間に初日は含めなくていいのではないでしょうか。

個人的には、先のページに書かれていた

★その２：Ｙ・Ｍ・ＭＤ・ＹＤ・ＭＤ・Ｄ対応

の式を利用(長くなりますが)して(それだけ別のセルで計算させてやればそれ以外の式は長くならないでしょう）

Mの式で月数を計算して
INT(式の結果/12)で年数
MOD(式の結果,12)で月数
繰上げは
月数+(A1<>A2)
でいいのではないかと思います。

A1が2013/1/31
A2が2013/2/1

で、0年1ヶ月となりますが、「1ヶ月未満の端数は1ヶ月に切り上げる」という条件ですので、いいのではないかと思います。

しかし、在籍日数で計算だったら楽でしたよね。

お礼

丁寧なご指導有り難うございます。
在籍期間の初日の扱いについても勉強になりました。
リトライしてみたいと思います。
有り難うございました。

回答No.11

bunjii

>Ａ1セルに2012/8/29
>Ａ2セルに2013/8/28 と試したところ、Ｄ3が「-1」となってしまいました。
確かに矛盾しますね。
期間によって誤差が生じるのかも知れません。
別の見方をすれば基本的には月数を算出してから年数に変換する必要があるかも知れません。

>またご回答頂きましたＡ3セル（在職期間）の関数は何を使われたのでしょうか？
対比する意味であなたが質問に記載された式を使っています。

お礼

ご回答有り難うございました。
お手数をおかけしました。

回答No.10

tom04

No.6・9です。

＞入社日の1日前からとする場合、式のどこに-1としたら良いのでしょうか？
は前回の数式で示したように

DATEDIFの　A1　の部分を　A1-1　とするだけで1日前になり、
開始日（入社日）の1日前になります。

おそらくこれをA3セルに当てはめるとお望みの結果になるのではないでしょうか？
細かい検証はしていませんので、
ご希望通りにならなかったらごめんなさいね。m(_ _)m

お礼

有り難うございました。
単純な質問ばかりですみませんでした。

回答No.9

tom04

No.6です。
まず
＞「入社日と退職日は在職日数に含む」とすれば、求めた在職日数が-1日になってしまい悩んでいます
の件について

入社日・退社日も含むのであれば、入社日の1日前からの計算にしてみてはどうでしょうか？

=IF(DAY(A1)=DAY(A2),DATEDIF(A1,A2,"Y")&"年"&DATEDIF(A1-1,A2,"YM")&"ヶ月",DATEDIF(EDATE(A1-1,-1),A2,"Y")&"年"&DATEDIF(EDATE(A1-1,-1),A2,"YM")&"ヶ月")

といった感じになります。

次に
＞月数の合計×基本給」になってしまい、大変な金額になってしまいます。
について・・・

おそらく前回の結果を12で割ったもので計算すればお望みの数値になると思いますが、
敢えて、その数値をセルに表示させるとすれば

=LEFT(A4,FIND("年",A4)-1)+MID(A4,FIND("年",A4)+1,FIND("ヶ月",A4)-FIND("年",A4)-1)/12

ではどうでしょうか？m（_ _）m

お礼

tom04さん、有り難うございました。

端数切り上げの（入社日・退職日を含む）問題、解決しました。

もう一つ教えて下さい。
この時のＡ3セルの在職期間について、入社日の1日前からとする場合、式のどこに-1としたら良いのでしょうか？

金額計算について、「おそらく前回の結果を12で割ったもので計算すれば…」ご指摘の通りです。
お恥ずかしい限りですm(_ _)m　単純な事でした。難しく考えすぎていました。
丁寧なご指導有り難うございます。

回答No.8

bunjii

>この計算では在職期間が○年11ヶ月○日となった場合、求めた在職年数は○年12ヶ月となってしまい、年に繰り上がりません。
基本的なことだけお伝えします。
日付の計算には0日がありませんので期間の計算に日付の値を使うことで思わぬ落とし穴に落ちます。
あなたが使っているDATEIF(A1,A2,"ym")は年と月の値で差を求めています。
従って端数の扱いはできません。
また、日数の計算でDATEIF(A1,A2,"md")としていますが、この式では起算日が含まれませんので、入社日の前日を起算日にしなければならず、誤解を招きます。（入社日と退職日は在職日数に含む）

端数繰り上げはROUNDUP関数を使うと余分な手数を必要としません。
A1セルに入社日、A2セルに退職日が入力されているとして
勤続年数（端数切捨て）=ROUNDDOWN(YEARFRAC($A$1,$A$2+1,1),0)
端数月数（端数繰り上げ）=ROUNDUP(MOD(YEARFRAC($A$1,$A$2+1,1),1)*12,0)-1

貼付図の上は勤続年数を算出したセルの状態です。
同じく下は端数の月数を算出しています。
後はあなたの応用力次第です。

お礼

bunjiiさん、ご回答有り難うございました。

ご指摘通り「入社日と退職日は在職日数に含む」を考慮し試して見ました。
Ａ1セルに2012/8/29
Ａ2セルに2013/8/28 と試したところ、Ｄ3が「-1」となってしまいました。
ROUNDUP関数のコマンド自体がわかりませんので、原因がつかめません。

またご回答頂きましたＡ3セル（在職期間）の関数は何を使われたのでしょうか？
よろしくお願いします。

回答No.7

kmetu

No6さんの考え方「「起算日」と「退職日」の日付部分が同じ日以外はすべて切り上げ」もいいかもですね。

とすればDATEDIFを使わない方法としては

=((YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1))+(DAY(A2)<>DAY(A1))

もありかも

ただ、起算日2013/1/31退職日2013/2/1で2ヶ月と計算されてしまいますが…

お礼

kmetuさん、度々のご回答有り難うございます。
最後の「…2ヶ月と計算されてしまいますが」は困ってしまいますね。。残念です(T-T)

回答No.6

tom04

こんばんは！

＞在職年数に端数がある時は月割りで計算し、1ヶ月未満の端数は1ヶ月に切り上げる。

すなわち「起算日」と「退職日」の日付部分が同じ日以外はすべて切り上げと考えてみてはどうでしょうか？

そういった方法での一案です。

↓の画像のA4セルに
=IF(DAY(A1)=DAY(A2),DATEDIF(A1,A2,"Y")&"年"&DATEDIF(A1,A2,"YM")&"ヶ月",DATEDIF(EDATE(A1,-1),A2,"Y")&"年"&DATEDIF(EDATE(A1,-1),A2,"YM")&"ヶ月")

という数式を入れています。
「起算日」の日付と「退職日」の日付が異なる場合は「起算日」の1か月前からの計算としています。

A5セルはA4セルの文字列を利用して
=LEFT(A4,FIND("年",A4)-1)*12+MID(A4,FIND("年",A4)+1,FIND("ヶ月",A4)-FIND("年",A4)-1)

という数式を入れています。
後はA5セルの数値を利用して
A5×基本給×係数
としてみてはどうでしょうか？m(_ _)m

お礼

tom04さん、ご回答有り難うございました。
早速試して見ました。
端数日数を切り上げる事に成功しました。

但しA5セルは月数の和を求めるのでなく、あくまで「年」と「月数」を出して計算します。
そうしないと、「月数の合計×基本給」になってしまい、大変な金額になってしまいます。

またNo.8のbunjiiさんのご指摘通り、「入社日と退職日は在職日数に含む」とすれば、求めた在職日数が-1日になってしまい悩んでいます。

回答No.5

kmetu

またまた　ごめんなさい

DAY(A2-A1)はダメでしたすみません。

代替の式は
http://www.geocities.jp/chiquilin_site/data/100509_datedif.html
を参考にしてみてください。

お礼

kmetuさん、本当に色々試して下さり有り難うございます。バグ情報も助かります。目下色々なご回答を頂き試しているところです。取りあえず御礼申し上げます・