[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『入社日から表彰基準日までの勤続年数..』（ハツネ）
[入社日から表彰基準日までの勤続年数、とりわけ休職等の除算期間を差し引いた実勤続年数の計算式がよくわかりません]
私のﾉｰﾄﾊﾟｿｺﾝではExcel2002、WindowsXPを使用しております。さて、Excel2002を使ってE列に「実勤続年数」を計算し、○年○月○日と表示いたしたいと思っておりますが、うまくいきませんのでご教示いただければありがたいと思います。
A入社日 B表彰基準日 除算期間 E実勤続年数
C始期 D終期
１ 1985/4/1 2005/11/3 1987/7/1 1987/12/31 ○年○月○日
２ 1985/4/1 2005/11/3 1989/7/1 1990/3/31 ○年○月○日
なかなか回答がないようですので、名案はありませんが恥を忍んで「珍回答」を披露させていただくことにします。
まず、最初につまづいたのが、日数の端数処理の問題です。 ご質問の除算期間の例で言えば、1987/7/ 1→1987/12/31を6ヶ月ちょうどとしたいのだろうと思いますが、 その場合の数式は、=DATEDIF(A1,B1+1,"md") でできそうです。
ただ、この数式を他の例にあてはめるとこのようになります。、
例1 1987/7/ 1 → 1987/12/31 0 例2 1987/6/30 → 1987/12/31 2 例3 1987/6/30 → 1987/12/30 1 例4 1987/6/30 → 1987/12/29 0
一つの期間でこういう誤差があるところにもってきて、 勤続期間の端日数(a)－除算期間の端日数(b) の計算をさせる場合には、 a>=b の場合は単純な引き算が出来ますが、b>aの場合はどうしたらいいのでしょうか？ つまり、31-a+b か 30-a+b か あるいは28(又は29)-a+b か、はたまたその月によって計算式を変えるのか？ あるいはこんなことを考えること自体が無意味なのでしょうか。
正直言って分からなくなりました。 もしかしたら、とんでもない勘違いをしているのかもしれないと思って不安になってきました。
ただ、分からないだけでは回答にならないので、 とにかく 1.期間の計算は DATEDIF関数に任せる。 2.端日数の繰り下げは 30-a+b で計算する。 という前提で考えてみました。
A B C D E
1 入社日 表彰基準日 除算開始日 除算終了日 実勤続年月日数
2 1985/4/1 2005/11/3 1987/7/1 1987/12/31 20年1ヶ月3日
3 1985/4/1 2005/11/3 1989/7/1 1990/ 3/31 19年10ヶ月3日
E2=DATEDIF(A2,B2+1,"y")-DATEDIF(C2,D2+1,"y")
-((DATEDIF(A2,B2+1,"ym")-(DATEDIF(C2,D2+1,"md")>DATEDIF(A2,B2+1,"md")))<DATEDIF(C2,D2+1,"ym"))&"年"
&IF(DATEDIF(A2,B2+1,"ym")-(DATEDIF(A2,B2+1,"md")<DATEDIF(C2,D2+1,"md"))<DATEDIF(C2,D2+1,"ym") ,12-DATEDIF(C2,D2+1,"ym")+DATEDIF(A2,B2+1,"ym"),DATEDIF(A2,B2+1,"ym")-(DATEDIF(A2,B2+1,"md") <DATEDIF(C2,D2+1,"md"))-DATEDIF(C2,D2+1,"ym"))&"ヶ月"
&IF(DATEDIF(A2,B2+1,"md")<DATEDIF(C2,D2+1,"md"),30-DATEDIF(C2,D2+1,"md")+DATEDIF(A2,B2+1,"md") ,DATEDIF(A2,B2+1,"md")-DATEDIF(C2,D2+1,"md"))&"日"
多少見やすくするために改行及び空白行を入れてみましたが、一つの数式です。 この数式をご覧になった方は、解読不能・・というか解読する気にもならないでしょうね。（汗) 数式の細かい検証はしておりませんが、ご希望の結果となりますでしょうか。 無責任な回答になってしまいましたが悪しからずご了承下さい。 (sato)
私も勉強のつもりで考えてみました。多分もっとスマートな回答が出て来るとは思いますが。 入社日～表彰基準日は =DATEDIF(A2,B2,"d") 除算開始日～除算終了日は =DATEDIF(C2,D2,"d")になると思います。 そして除算期間を除いた日数は上記式の引き算 =DATEDIF(A2,B2,"d")-DATEDIF(C2,D2,"d") で求まると思います、この日数を３６５日で割り年数を求めます。 =QUOTIENT(DATEDIF(A2,B2,"d")-DATEDIF(C2,D2,"d"),365) 残りの一年未満の端数の日数は =MOD(DATEDIF(A2,B2,"d")-DATEDIF(C2,D2,"d"),365) としてみました。これはあくまでも一年を３６５日と考えた場合ですが。 (KI)
satoさん、私の質問に真剣に取り組んでいただきありがとうございます。私は初心者ですので、正直申し上げてsatoさんの案はとても難解で解読する 気になりませんでした。 kiさんにもお礼申し上げます。kiさんの案なら可能に思えましたので試みました。ところが、 =QUOTIENT(DATEDIF(A2,B2,"d")-DATEDIF(C2,D2,"d"),365)の数式を入れると「＃NAME？」の表示が出て年数が表示されませんでした。 どうしてなのでしょうか。（ハツネ）
おはようございます。 私のExcel 2000ではエラーは出ませんが？ 20と出てきますが。 ちなみに、=DATEDIF(A2,B2,"d") はどうですか？ エラーになりますか？ (KI)
QUOTIENTのHelpに 『この関数が使用できず、エラー値 #NAME? が返される場合は、分析ツール アドインを組み込む必要があります。』 との記述があります。 (Hatch)
ハツネさん、誠に分かりにくい数式を書いてしまいまして、申し訳ありませんでした。 ただ、考え方としてはいたって単純で稚拙な方法なんです。
A B C D
1 入社日 表彰基準日 除算開始日 除算終了日
2 1985/4/1 2005/11/3 1987/7/1 1987/12/25
3
4
5 年数 月数 日数
6 勤続年月日 20 7 3
7 除算年月日 0 5 25
8 実勤続年月日 20 1 8
まず、勤続年月日と除算年月日をそれぞれ年、月、日に分けて求めます。
B6=DATEDIF(A2,B2+1,"y") C6=DATEDIF(A2,B2+1,"ym") D6=DATEDIF(A2,B2+1,"md")
B7=DATEDIF(C2,D2+1,"y") C7=DATEDIF(C2,D2+1,"ym") D7=DATEDIF(C2,D2+1,"md")
次に、勤続年月日から除算年月日を差し引いて実勤続年月日を求めます。 計算の方法は普通に手計算すると同じように、「日数」のほうから先に求めます。 上記の例ではD6の3から、D7の25を引くわけです。
このとき、3から25は引けませんので隣の1ヶ月から25を引いて3を足すという、 小学校の引き算の原理なんですが、1ヶ月を何日とするかで分からなくなったわけです。 ここではその点は深く考えずに（汗）、1ヶ月30日として計算しています。
D8=IF(D6<D7,30-D7+D6,D6-D7)
月数及び年数は、単純に引き算は出来ませんね。 この例では月数は7-5ではなくて、6-5にしないといけませんね。 その上でC7がC6より大きくなったらやはり年の桁を減らして引き算しないといけないですね。
C8=IF(C6-(D6<D7)<C7,12-C7+C6,C6-(D6<D7)-C7)
最後に以上のことを考慮したうえで、年数の引き算をします。
B8=B6-B7-((C6-(D7>D6))<C7)
このB8からD8までの式をつないで間に「年・ヶ月・日」の文字を入れたものが最初に書いた わけの分からない長い式なんです。 単純に日数だけの引き算では誤差が出ると思いましたので、このような考え方をしてみました。 (この方法なら誤差がでないというわけでもないんですが・・・) (sato)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.