[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日曜・祝日を抜いた6ヵ月後、1年後の日付を自動表示させたい』（Hana28）
A1にある登録年月日を入力後、B1・C1にA1の日付からの6ヵ月後、1年後の日付を表示させたいのですが、 日曜祝日抜きの条件がうまく書けませんでした。。
数カ月後の表記、はEDATEというをこちらで見つけたのですが、日曜と祝日をどのように抜けばよいのかが分からず・・・。 探したりない、と思うのですが、もしお力をお借りできれば、、とも思い登録しました。 よろしくお願いします。
Excel2007です。
一つ質問です。 日曜・祝日を考えない場合、「2010/8/31」の6ヶ月後はいつですか？ (独覚)
すみませんが確認のためもう一つ質問です。 「2009/08/01」の6ヶ月後は「2010/2/1」でしょうか？ それとも「2010/2/2」でしょうか？ (独覚)
2009/8/1の6ヵ月後は2010/2/1としておりました。 ご連絡が遅れてすみません。また、フォントサイズもおかしく 申し訳ありません。。
まず、日曜・祝日を考慮しない場合の式です。
6ヶ月後が =EDATE(A1,6)+(DAY(A1)<>DAY(EDATE(A1,6)))
12ヶ月後が =EDATE(A1,12)+(DAY(A1)<>DAY(EDATE(A1,12)))
ここで、条件が土・日・祝日抜きであればWORKDAY関数を使って割と簡単に求められるのですが日・祝日ということで 私自身ではうまく作れなかったため下記のリンク先のKAMIYAさんの式を使わせてもらいました。
[[20040923012114]] 『NETWORKDAYSで休日を日祝だけにしたい』（ど素人です。）
まず、祝日の表を作っておいてその表に名前を付けておきます。 (例として「祝日」) そして6ヶ月後が
=SMALL(IF((COUNTIF(祝日,EDATE(A1,6)+(DAY(A1)<>DAY(EDATE(A1,6)))-1+ROW($1:$20))=0)*(WEEKDAY(EDATE(A1,6)+(DAY(A1)<>DAY(EDATE(A1,6)))-1+ROW($1:$20))<>1),EDATE(A1,6)+(DAY(A1)<>DAY(EDATE(A1,6)))-1+ROW($1:$20)),1)
12ヶ月後が
=SMALL(IF((COUNTIF(祝日,EDATE(A1,12)+(DAY(A1)<>DAY(EDATE(A1,12)))-1+ROW($1:$20))=0)*(WEEKDAY(EDATE(A1,12)+(DAY(A1)<>DAY(EDATE(A1,12)))-1+ROW($1:$20))<>1),EDATE(A1,12)+(DAY(A1)<>DAY(EDATE(A1,12)))-1+ROW($1:$20)),1)
どちらの式も通常Enterで式を確定するところをShift+Ctrl+Enterで確定してください。
追記です。 書き込む行の先頭に半角スペースを入れるとフォントサイズがそろいます。
追記の2 EXCEL2010では「WORKDAY.INTL」という関数が追加されていてこの関数を使うと今回の計算も簡単にできるようです。
(独覚)
独覚様 ありがとうございます。大変遅くなりましたが、お教えいただきました式で６ヶ月のデータ、 作成できました！GWもちゃんとはじいてくれました！！ 祝日のリスト作成で時間がかかってしまい、せっかくお教えいただいたのい、ご連絡が遅く、申し訳ありませんでした。。 EDATE,COUNTIFは使ったことがあるのですが、ROWやSMALLは初めてで、且つ、<>も初めてです。 これから上記の式内の関数を調べ、構成を理解できるよう頑張ります。 お忙しい中、ご教示ありがとうございます。大変助かりました。 また、フォントサイズの件もありがとうございます。１つ覚えることができました。 (Hana28) 先ほど、GWもはじけた、、と記載しましたが、間違えてました。。 もうちょっと確認してみます。すみません。 やはり、GW平日（5月3日～5月5日）は上手くはじけませんでした。 が、ここだけ手入力ではじき、データ完成しました！！ありがとうございます。 （Hana28)
こんばんは。 解決済みですので、蛇足ですが。 ---------- 6ヵ月後の日付とは、 1.基本的には、登録年月日の6ヵ月後の同日で、 ただし、6ヵ月後の同日が、 2.ありえない日付（2/31など）のときは、翌月1日とし、 さらに、1.2.後の日付が、 3.日曜または祝日の場合は、直後の平日（土曜含む）とする ということでしょうかね。
1.2.については、独覚さんのご回答を使わせていただいて、 3.については、1.2.後の日付が、 ・土曜日ならそのまま、 ・土曜以外ならWORKDAY関数で平日を求める としてみました。 （1つの数式の中で、同じ日付計算を3回もしているので、ほんとは名前定義なり、 作業列なりで、もっと数式を短くしたいところですが）
[R/C] [A] [B] [C] [D] [E] [1] 登録年月日 6ヵ月後(除日祝) 1年後(除日祝) 6ヵ月後 1年後 [2] 2010/08/31(火) 2011/03/01(火) 2011/08/31(水) 2011/03/01(火) 2011/08/31(水) [3] 2009/08/01(土) 2010/02/01(月) 2010/08/02(月) 2010/02/01(月) 2010/08/01(日) [4] 2009/08/06(木) 2010/02/06(土) 2010/08/06(金) 2010/02/06(土) 2010/08/06(金) [5] 2009/08/07(金) 2010/02/08(月) 2010/08/07(土) 2010/02/07(日) 2010/08/07(土) [6] 2009/08/29(土) 2010/03/01(月) 2010/08/30(月) 2010/03/01(月) 2010/08/29(日) [7] 2009/08/30(日) 2010/03/01(月) 2010/08/30(月) 2010/03/01(月) 2010/08/30(月) [8] 2009/05/03(日) 2009/11/04(水) 2010/05/06(木) 2009/11/03(火) 2010/05/03(月) [9] 2009/11/03(火) 2010/05/06(木) 2010/11/04(木) 2010/05/03(月) 2010/11/03(水)
B2 =IF(WEEKDAY(EDATE(A2,6)+(DAY(A2)<>DAY(EDATE(A2,6))))=7,EDATE(A2,6)+(DAY(A2)<>DAY(EDATE(A2,6))),
WORKDAY(EDATE(A2,6)+(DAY(A2)<>DAY(EDATE(A2,6)))-1,1,祝日))
C2 =IF(WEEKDAY(EDATE(A2,12)+(DAY(A2)<>DAY(EDATE(A2,12))))=7,EDATE(A2,12)+(DAY(A2)<>DAY(EDATE(A2,12))),
WORKDAY(EDATE(A2,12)+(DAY(A2)<>DAY(EDATE(A2,12)))-1,1,祝日))
↓参考
D2 =EDATE(A2,6)+(DAY(A2)<>DAY(EDATE(A2,6)))
E2 =EDATE(A2,12)+(DAY(A2)<>DAY(EDATE(A2,12)))
※祝日については、↓のサイトからコピペさせていただきました。 http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/hiduke03.html （コタ）
> 昨年の8月31日の登録は今年の3月1日といたしました。
これは残っているデータを見て答えられたのでしょうか？ 3月1日になっているのは、今年2010年の2月28日が日曜日だからということはありませんか？ 2月28日が日曜日以外でも、本当に3月1日でいいのでしょうか？ （R）
>やはり、GW平日（5月3日～5月5日）は上手くはじけませんでした。 これは元の日付、および祝日リストに登録した日付はそれぞれ何だったのでしょうか？
コタさん、 土曜日が祝日の場合の処理が抜けてませんか？ Rさん あっ、確かにありそうですね（汗 私の確認不足かも。
追記 コタさんの式ですが、上記以外でも例えば6ヶ月後、1年後が金曜日で祝日だった場合、土曜日ではなく月曜日になってしまいますね。 (独覚)
独覚さんへ ご指摘ありがとうございます。
＞ 土曜日が祝日の場合の処理が抜けてませんか？ ＞ 例えば6ヶ月後、1年後が金曜日で祝日だった場合、土曜日ではなく月曜日になってしまいますね。 あーたしかに、おっしゃるとおりですね。 土曜日が祝日の場合だけなら、COUNTIFで祝日判定すれば回避できそうですが。。 やはり、WORKDAY関数ではきれいにできないのでしょうかね。 （でもしつこく考えたりしますが^^）
（コタ）
独覚様、コタ様 R様 皆さまお忙しい中、ありがとうございます。出張中PCの設定がうまくできず、ご連絡、いえ、御礼が遅くなり大変申し訳ありませんでした。 GWは4月29日～5月5日に設定いたしましたが、やはり「自動」はできませんでした。 が、GWだけでしたので、作成完了いたしました。 また、R様ご質問の3月1日は、既存データからの抽出で、ご指摘通り、 2月末が日曜のため、と思われます。今回のデータ作成は完了いたしましたが、 まずは、お教えいただきました関数の構成を週末で覚えたいと思います。 お忙しい中、ありがとうございます。本当に助かりました。（Hana28)
まず、「2月末が日曜のため、と思われます。」ということであれば式の方は
6ヶ月後 =SMALL(IF((COUNTIF(祝日,EDATE(A1,6)-1+ROW($1:$20))=0)*(WEEKDAY(EDATE(A1,6)-1+ROW($1:$20))<>1),EDATE(A1,6)-1+ROW($1:$20)),1)
12ヶ月後 =SMALL(IF((COUNTIF(祝日,EDATE(A1,12)-1+ROW($1:$20))=0)*(WEEKDAY(EDATE(A1,12)-1+ROW($1:$20))<>1),EDATE(A1,12)-1+ROW($1:$20)),1)
にしないと月末日付の場合に正しくない値になっている可能性があります。
あと、GWについてですが祝日の表の表示形式で年まで表示させてみてください。 祝日表では月日だけではなく年も参照するので年があってない場合祝日とみなされません。
例えば「2010/5/1」が登録してあって「2011/5/1」が登録してない場合、「2011/5/1」は祝日とはみなされません。 (独覚)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.