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

質問

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.4

kmetu

たびたびすみません

さっき他で見たんですが
なんかエクセル2007のDATEDIFにはバグがあるみたいですね。

https://www.google.co.jp/search?q=datedif+%E3%83%90%E3%82%B0&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:ja:official&hl=ja&client=firefox-a#hl=ja&q=%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB2007%E3%80%80datedif+%E3%83%90%E3%82%B0&rls=org.mozilla:ja%3Aofficial

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

こちらの式のほうがいいかもしれません。

回答No.3

kmetu

補足追加です

切り上げた全月数は

=DATEDIF(A1,A2,"ｍ")+(DATEDIF(A1,A2,"md")>0)

としたほうが短くていいですね。

回答No.2

kmetu

> (2)在職年月に基本給を掛けて、更に係数を掛けて退職金を求める。
> 　（○年＋○ヶ月/12）×基本給×係数　となります。

が出せればいいんですよね。

=YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1)

で全月数が求められます。端数日数を1ヶ月単位に切り上げる部分は質問者さんの式を利用して

=((YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1))+(DATEDIF(A1,A2,"md")>0)

として切り上げた月数を求めます。

以降上記の式そのまま書くと長くなるので、便宜上以後 X と表示します。

（○年＋○ヶ月/12）×基本給×係数

の○年は

=INT(X)

で求められます。

○ヶ月は

=MOD(X)

で求められます。

こういう出し方でいかがでしょうか。

各関数の意味はHELPなどで調べてくださいね。

回答No.1

neKo_deux

> 在職期間
> 在職年数
> 在職年月

これらは別々のものなのか？同じものを指すのか？ってな所から、問題を整理した方が良いと思いますが。

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

> (1)の計算は次のように求めてみました。
> 　=DATEDIF(A1,A2,"ｙ")&"年"&DATEDIF(A1,A2,"ｙｍ")+(DATEDIF(A1,A2,"md")>0)&"ヶ月"

せっかく質問文の上から３行目の式で在職期間の年、月、日を計算してるのに、改めてDATEDIFする必要は無いのでは。
３行目の式で在職期間を計算し、A3にでも入れたのなら、
A4に在職期間の年数
A5に在職期間の月数
A6に在職期間の日数
とかって数字を取り出せば、
A8に端数日数を切り上げた場合の月数
A7に端数日数を切り上げた場合の年数
が計算しやすいのでは？

式一本で書ければシンプルではありますが、エラーなんかがあっても原因が分かりづらいし、無理する必要性って無いと思いますが。

お礼

早速のご回答ありがとうございます。
私も結局はご回答のような方法がベストかなと思いつつ、もう少し深入りした手立てを模索してしまいました。参考にさせて頂きます。ありがとうございました。