3ヵ月後の日付を自動計算しようと思っているのですが、上手くいかなかったのでご質問させていただきます。
3ヵ月後の日付をエクセルで自動的に計算させようと思って、いろいろ試行錯誤しているのですが、上手くいかなかったので、ここに質問させていただきました。よろしくお願いいたします。
で、単純に3ヵ月後を計算させようと思い、ExcelのWorkday関数等を利用して、以下のような式を考えました。
=WORKDAY(DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)-1),1,B1:B38)
ここで、セルA1には基準日を入力し、セルB1からB38には国民の祝日を入力しています。
で、これでほとんどの場合で上手くいったんですが、例えば明日8月31日を基準日とした場合、3ヵ月後を、基準日が月末なので3ヵ月後も月末で揃える格好で日付を指定したいと考えており、エクセル関数の組み合わせでとまってしまいました。
つまり8月31日を基準日としたような場合は、求める日付として11月30日(11月30日が土日や祝日の場合は、更にさかのぼって11月29日だったり、11月28日が求める日付)となるようにしたいと考えております。
どなたか、お知恵を拝借できればと思っております。よろしくお願いいたします。
No.5ベストアンサー
- 回答日時:
No.1,2です。
基準日の3ヶ月後が土日祝日の場合、前にずらすのか後ろにずらすのか?
質問文の例だと前にずらすとしか解釈できませんでしたので、No.2では
=WORKDAY(EDATE(A1,3)+1,-1,B1:B38)
と回答しました。
これは、3ヶ月後の日付が土日祝日でなければそのまま3ヶ月後の日付
土日祝日の場合は前にずらした日付、となります。
ここで+1と-1を変えて
=WORKDAY(EDATE(A1,3)-1,1,B1:B38)
とすれば、逆に後ろにずらした日付も計算できます。
「基本は後ろにずらすが、後ろにずらすと月をまたいでしまう場合のみ、前にずらす」
ということでよろしいのでしょうか?
一つの式でやるとややこしくなるので作業列を使いますが
C1に
=WORKDAY(EDATE(A1,3)+1,-1,B1:B38)
D1に
=WORKDAY(EDATE(A1,3)-1,1,B1:B38)
E1に
=MONTH(C1)
F1に
=MONTH(D1)
G1に
IF(E1<>F1,C1,D1)
度々の回答、有難うございました。
「基本は後ろにずらすが、後ろにずらすと月をまたいでしまう場合のみ、前にずらす」
という一文が、私の質問に抜けていたため、お手間を取らせてしまい、申し訳ありませんでした。
If文で、基本の場合(後ろにずらす)と、例外(月をまたぐので前にずらす)の場合わけをして、処理されている点と、同僚が見てもこちらの方が分かりやすいということでベストアンサーに選ばせていただきました。
また、なにか相談するようなときは、よろしくお願いします。
今回は有難うございました。
No.4
- 回答日時:
8月31日の3ヶ月後に日付を11月30日にしたいとのことですと、8月30日の3ヶ月後と同じになりますがそれでもよいのでしたら次のような式になりますね。
=IF(A1=EOMONTH(A1,0),WORKDAY(DATE(YEAR(A1),MONTH(A1)+4,0)-1,1,B1:B38),WORKDAY(DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)-1),1,B1:B38))
11月30日の3ヶ月後は2011年2月28日になりますね。
回答誠に有難うございました。
基準日が月末の場合、4ヵ月後から計算し、月末で無い場合は、(通常通り)3ヵ月後を計算するという趣旨だと拝察しました。
ベストアンサーに選ぼうかとも思いましたが、同僚と相談して別の方にさせていただきましたが、私の中では、Myベストアンサーです。
大変有難うございました。また何か相談した時は、よろしくお願いします。
No.3
- 回答日時:
まず、確実に月末を求める考え方ですが、これは3ヵ月後の月末を直接求めるのではなく、4ヵ月後の初日の前日というように考えます。
A列 B列 B列の式
A5 年月日 2010/8/31
A6 年 2010 =YEAR(B5)
A7 月 8 =MONTH(B5)
A8 日 31 =DAY(A1)
A9 4月後の1日2010/11/1 =DATE(B6,B7+3,1)
A10 3月後の月末2010/10/31 =B9-1
ただし、10月以後は3月後は翌年になりますのでYEAR関数だけではうまくいきません。この解決は考えてください。
週末の判定は上記の月末の日が求められたら、WEEKDAY関数でその月末日の曜日がわかります。
これにIF関数を組み合わせれば、その結果から1日前か2日前をとることができます。
以上が考え方ですが、後はご自分で工夫してください。
関数はいかに使える関数を組み合わせるかがポイントで、頭の体操としてはなかなか面白いと思います。ひとつわかれば次のアイデアが出てきますので、是非がんばってください。
ご回答ありがとうございます。
『関数はいかに使える関数を組み合わせるかがポイントで、頭の体操としてはなかなか面白いと思います。』という部分には、非常に共感できますので、頑張っておりますが、正直申し上げてご紹介いただいた方法は、一番最初に試したもので、、、、、、、、、ちょっと評価は・・・・・・とさせて下さい。
質問には詳しく書いていなかったので、こういうことを招いたんだと思いますが、ご紹介いただいた方法を実は一番最初に試して、上手くいかなかったので分析ツールのWorkday関数を引っ張ってきて、現在に至る、という流れになっております。
もし、この先のアイデア等ございましたら、またご回答いただければと存じます。
今回はありがとうございました。
No.2
- 回答日時:
No.1です。
考えてみたら、WORKDAY関数を使っているということは
分析ツールのアドインを有効にしているんですよね。
でしたらEDATE関数が使えますね。
その場合は以下のような式になります。
=WORKDAY(EDATE(A1,3)+1,-1,B1:B38)
No.1の回答は忘れてください。失礼しました。
早速の、ご回答ありがとうございます。
すぐさま試してみたところ、たしかに8月31日の3ヵ月後が11月30日になりました。
が、私の説明が拙かったせいもあるのかと思いますが、普通の3ヵ月後、例えば8月13日の3ヵ月後を考えた場合、11月15日が求めたい日付なのですが、11月12日が計算されるようで、上手くいっておりません。
というわけで、お返事が遅くなりましたが、依然として試行錯誤中です。
もし、なにか良いアイデア等あれば、またご回答いただければと存じます。
今回はありがとうございました
No.1
- 回答日時:
=WORKDAY(IF(MONTH(DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)))<>MONTH(A1)+3,DATE(YEAR(A1),MONTH(A1)+4,0),DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)))+1,-1,B1:B38)
こんな感じでどうでしょうか。
3ヶ月後の日付を「DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))」
で計算してしまうと、3ヵ月後の同じ日付がない場合に
さらに翌月の日付になってしまうので
(8月31日の3ヵ月後は、12月1日になってしまいますよね)
IFで条件分岐して「DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))」の月部分と
A1の月部分+3を比較して、一致しない場合は
「DATE(YEAR(A1),MONTH(A1)+4,0)」で3ヵ月後の月末日付
一致する場合はそのまま3ヵ月後の日付
これに1日をプラスした日付をWORKDAY関数の開始日として、日数を-1とする
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/01/10 09:15
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- その他(Microsoft Office) エクセルの休日について教えてください。 1 2023/01/06 15:45
- Excel(エクセル) Excel ある日の45日後の計算 及び 3か月後の計算 6 2022/04/05 13:44
- Excel(エクセル) 現時点の年齢を算出して、その年齢と一致したセルを色付けしたい。 4 2022/06/23 17:49
- Excel(エクセル) ExcelにてA1セルに1、B1セルに月と入力した際に、A列に日付、B列に曜日が入力される様になる為 1 2023/03/02 06:47
- Excel(エクセル) エクセルで月末、月初の判定をしたい。 4 2022/05/18 23:22
- Excel(エクセル) エクセル関数について 2 2022/04/13 18:25
- Excel(エクセル) 下記エクセルの式がなぜこうなるのか理由が知りたいです。 6 2022/08/20 00:43
- Excel(エクセル) エクセルの数式で教えてください。 3 2022/12/22 17:29
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel2010の並べ替えで行の高さ...
-
エクセル関数で {=TABLE(,セル...
-
リース初心者です 利子率の計...
-
エクセルで離れたセルを離れた...
-
VBA 条件が一致した場合の...
-
至急! Excelで歩合計算
-
プルダウンで選択すると隣のセ...
-
エクセルで行ごとに繰り返しの...
-
Excelの空白行を上に詰めるVBA...
-
エクセルデータをワードへ反映...
-
エクセルで一番下の日付や時刻...
-
Excelで歩合給の計算をしたいの...
-
エクセルシートを重ねることは...
-
エクセル関数 2文字目に空白...
-
エクセルの行高さが、挿入作業...
-
エクセルで作った書類に、パン...
-
今日の日付が第n曜日かを求める
-
エクセルの計算式ですが・・・
-
エクセル フォームボタンクリ...
-
Excelハイパーリンクのアドレス...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel2010の並べ替えで行の高さ...
-
リース初心者です 利子率の計...
-
エクセルで複数の勤務時間ごと...
-
エクセル関数で {=TABLE(,セル...
-
プルダウンで選択すると隣のセ...
-
エクセルで離れたセルを離れた...
-
60進法で複数セルの足し算、引...
-
エクセルで作った書類に、パン...
-
VBA 条件が一致した場合の...
-
Excelハイパーリンクのアドレス...
-
エクセルデータをワードへ反映...
-
至急! Excelで歩合計算
-
Excelのオートカルクの結果をコ...
-
今日の日付が第n曜日かを求める
-
エクセルの行高さが、挿入作業...
-
Excelの空白行を上に詰めるVBA...
-
エクセル フォームボタンクリ...
-
エクセルで一番下の日付や時刻...
-
エクセルデータを拡大/縮小して...
-
エクセルで複雑な表の作り方
おすすめ情報