Google スプレッドシート ： 日付セルから、任意の期間をもとに「月」を表示させる方法

日付が入力されているとあるセルをもとに、隣のセルに月を表示させるには「month」関数を使えばいいかと思います。

ただ、今回わたしの方で必要な「月」の区切りが少し特殊で、毎月末5営業日前が締め日のため、その翌日から翌月末5営業日前までを「x月」と定義して表示をさせたいと思っています。

例
2016年12月28日（月末4営業日前の翌日）〜2017年1月25日（月末4営業日前）＝1月
2017年1月26日（月末4営業日前の翌日）〜2017年2月22日（月末4営業日前）＝2月
※「月末4営業日前の翌日」は営業日ではなく土日祝だったとしてもOK

この場合の日付（A列）を元にした「月」（B列）を表示させる関数のやり方をぜひ教えてくださいませ。祝日を別のブックのシートに定義しているという前提でやり方を教えていただければ幸いです。

質問者からの補足コメント

• 本当に丁寧なお返事をいただきありがとうございます。
  とてもありがたいです。
  
  まずそもそものところでこちらの記載ミスがありご迷惑をおかけいたしました。
  >４営業日前が月末でその翌日が月始め
  が正しいです。混乱を招き申し訳ありません。
  
  そして案1を選択し、カレンダーをつくってみています。
  途中まで順調だったのですが、
  
  >Cに=COUNTIF(INDIRECT("A○:A"&ROW()+DATE(YEAR(A○),MONTH(A○)+1,0)-A○),"営業日")で、月末までにその日を含めた営業日が何日あるかが表示される。
  
  をやってみたところ、「0」となってしまいました。
  画像を添付いたしますので、もし間違いがありましたらご指摘いただければ幸いです。

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2017/01/30 01:21

• 

  ↑すいません、間違えた場所に投稿してしまいました…

    補足日時：2017/01/30 01:23

No.1 ベストアンサー 回答者： yuji3690 回答日時： 2017/01/29 23:59

営業日の定義は何ですか？土日祝日を除く日と言う意味ですか？

5営業日前が月末でその翌日が月始めなのか、４営業日前が月末でその翌日が月始めなのかどちらですか？
1月の例では12/27が締めで火曜＝４営業日前。28水曜＝３営業日前。29木曜＝２営業日前。30金曜＝１営業日前。31土曜＝休み　でしょうか？
２月の例なら1/25が締めで水曜＝４営業日前。26木曜＝３営業日前。27金曜＝２営業日前。28土曜＝休み。29日曜＝休み。30月曜＝１営業日前。31火曜＝？？？　合わないのですが…？
締めを５営業日前にすると、2月の例は上手くいくけど、１月の例で31日が土曜だけど１営業日前？

とりあえず仮に５営業日前（営業日限定）が締めとして、翌日が営業日かどうか関係なく月始め。
で考えて見ます。

単純に本来の1日が5日目になるのであれば、B1=MONTH(A1+4)となりますね。
変更前　-4　-3　-2　-1　0　1　（月末が0となっているので締めは-4＝5営業日前）
変更後　締　 1　 2　 3　 4　5
判定　　 0　 1　 2　 3　 4　5

ここに土日祝日が含まれていた場合
変更前　-4　祝　-3　-2　土　日　-1　0　1　と数えたい（ということでいいんでしょうか？）
変更後　締　 1　 2　 3　 4　 5　 6　 7　8
判定　　 0　 1　 1　 2　 3　 3　 3　 4　5
B1=MONTH(A1+4+土日祝日の日数)

逆に言えば、その日から本来の月末までに何日土日祝日があるかを数えれば判定できる。

案１）
カレンダーのシートを作る。
1年分であれば365or366行、Aに日付、
Bに「IF(COUNTIF(祝日表,A○)=0,IF(WEEKDAY(A○,2)<6,"営業日","休"),"休")と言った感じ（もちろんA○はA1なりのセル指定）で書けば、営業日か休かを判断できる。
Cに=COUNTIF(INDIRECT("A○:A"&ROW()+DATE(YEAR(A○),MONTH(A○)+1,0)-A○),"営業日")で、月末までにその日を含めた営業日が何日あるかが表示される。
元の（月を表示させたいシートの）Bに
=MONTH(DATE(1,MONTH(A○)+IF(VLOOKUP(A○,カレンダーシート名!A:C,3,FALSE)<5,1,0),1))
で、月末までに営業日が5日未満であれば月に１追加して表示されます。

案２）
①＝DATE(YEAR(A○),MONTH(A○)+1,0)
月末の日付を表示しています
②=SUMPRODUCT((祝日シート!A○:A△>=A○)*1,(祝日シート!A○:A△<=①)*1)
祝日の一覧が祝日シート!A○:A△に日付で入力されているとしています。
入力した日以降で月末以前の祝日が一覧の内に何個あるか数えてます。
③=INT(((①-A○+1)/7)*2
月末までに丸何週あるか数えて2(土日)をかけてます。
④=①-③*7
月末まで丸何週の部分を除いて何日あるか
⑤=IF(④=0,0,IF(WEEKDAY(A○,2)<=6,IF(WEEKDAY(A○,2)+④-1>=6,1,0))+IF(WEEKDAY(A○,2)<=7,IF(WEEKDAY(A○,2)+④-1>=7,1,0)))
④がある場合にその中に土曜、日曜が含まれているなら計上。
⑥＝②+③+⑤
A○の日を含む月末までの土日祝日の数です。
⑦=MONTH(DATE(1,MONTH(A○)+IF((①-A○+1)-⑥＞4,0,1),1))
A○含め月末までの営業日が4日以内であれば月に１加えて、月を表示します。

補助セル使わずに1つのセルにまとめて押し込むこともできますが、後で見るとわけ分からないほど長くなると思います。

この回答へのお礼

本当に丁寧なお返事をいただきありがとうございます。
とてもありがたいです。

まずそもそものところでこちらの記載ミスがありご迷惑をおかけいたしました。
>４営業日前が月末でその翌日が月始め
が正しいです。混乱を招き申し訳ありません。

そして案1を選択し、カレンダーをつくってみています。
途中まで順調だったのですが、

>Cに=COUNTIF(INDIRECT("A○:A"&ROW()+DATE(YEAR(A○),MONTH(A○)+1,0)-A○),"営業日")で、月末までにその日を含めた営業日が何日あるかが表示される。

をやってみたところ、「0」となってしまいました。
画像をさきほど誤って補足のところに入れてしまいましたが、ご覧いただけると嬉しいです。
よろしくお願いいたします。

お礼日時：2017/01/30 01:25

No.2 回答者： yuji3690 回答日時： 2017/01/30 01:41

失礼しました。

AとBを間違えていた上に""の中にあるので下の行にコピーした際に○の数字が変わらない状態になっていました。
INDIRECT("A○:A"&ROW()+　の所を
INDIRECT("B"&ROW()&":B"&ROW()+　に修正してください。

この回答へのお礼

ありがとうございました。本当に助かりました！！！

お礼日時：2017/01/30 02:08