行程期間の月毎の日数を出す方法

質問

【状況】
4年分の作業工程表を作成しています。
期間を入力すると作業工程表に黒い色が設定されるように条件付き書式が設定されています。
【やりたいこと】
それぞれの月のセルに期間の日付を表示させたいです。
2024年1月は、1回目の作業の日数16日と2回目の日数の6日を足して21日と表示したい。
３回目の作業は2024年2月15日～3月28日、2月はうるう年なので14日と表示　3月のセルには29日と表示したい。

期間を求めるのは、datedif関数を使用するとよいのだと思いますが、1回目の作業工程と2回目の作業工程が同じ月なら合計しないといけないのとうるう年が絡んでくると何をどうしたらよいのかわからないです。
別シートに計算用のシートを作成しようと思いやってみたのですがうまくいきません。
VBAを使わずに計算式で作成したいと思っております。
ご存じの方がいらっしゃいましたらご教示お願い致します。

回答No.2

kkkkkm · ベストアンサー

A6に
=MONTH(A10)
として右にコピー

A7に
=IF(MONTH(A10)<>MONTH(B10),DATEDIF(A10,EOMONTH(A10,0),"d")+1,DATEDIF(A10,B10,"d")+1)
B7に
=IF(MONTH(A10)<>MONTH(B10),DATEDIF(EOMONTH(A10,0),B10,"d"),0)
として
各ブロックの開始と終了の列の7行目にコピー

A4
に
=SUMPRODUCT(($A$6:$L$6=A2)*($A$7:$L$7))
として右にコピー

10行目の日付は表示形式で2015/1/5を1/5にしていると考えています。
2行目と6行目は表示形式で同じ見た目にしてください。
DATEDIFはうるう年を認識しますので利用してます。

> 1回目の作業の日数16日と2回目の日数の6日を足して21日

これは22日と考えています。

添付画像はブロック3までしかデータを入れていません。

回答No.8

kagakusuki

>2024年1月は、1回目の作業の日数16日と2回目の日数の6日を足して21日と表示したい。

との事ですが、1回目の作業期間と2回目の作業期間の間には重複期間が無いのですから、2024年1月中で作業のある日数は21日ではなく、単純に1回目の作業の日数16日と2回目の日数の6日を合計した22日ではないでしょうか。

　それと、画像に写っている各セルが何というシート名のシート上のどのセル番号のセルなのかという事や、画像の上に写っている色分けバー表示のセルが何列ほどの列に亘るものなのかといった事が不明ですので、取り合えず下記の仮定に基づいた回答をさせて頂きます。

・画像の上に写っている色分けバー表示は、Sheet2のB列～NC列にかけての366列に亘って作表されている。

・画像で「2024年」という西暦年が入力されているセルはSheet2のB2セルを左端とした結合セルに入力されていて、そのずっと右手の方には「2025年」等の別の都市も記入されている。

・西暦年は「2024/1/1」などの日付をセルの表示形式を使って表示だけを「2024年」に見せかけたもの"ではなく"、表示されているものと同じ「2024年」という単なる文字列データの形で入力されている。

・画像で色分けバーの上に写っている「1月」などの月名ははSheet2の3行目中の結合セルに「1月」等の文字列データの形で入力されている。

・画像の下側に写っている表はSheet上に存在している。

・画像の下に写っている表中で「開始」や「終了」、「日数」などの項目名が入力されているセルの内、左端の「開始」と入力されているセルはSheet1のB3セルであり、同じく最初に「終了」と入力されているセルはSheet1のC3セル、最初に「日数」と入力されているセルはSheet1のD3セルである。

・画像の下に写っている表中で「1/5」などの日付はSheet1の4行目に入力されていて、シリアル値形式のデーターをセルの書式で日付表示に変換して表示されている。

・開始日や終了日には年が異なる日付も含まれており、例えば2箇所ある行程「1」の内、左端にある行程の開始日である「1/5」は「2024/1/5」に該当する"シリアル値"のデータ形式で入力されていて、同じく中列の少し右手にある行程「1」の開始日である「1/22」は「2025/1/22」に該当する"シリアル値"のデータ形式で入力されている。

・それぞれの月における作業のある日の日数はSheet2に表示するものとし、それを表示するセルは各月の列範囲の中で最も左端にある列の7行目のセルである。(例えば画像中で2024年1月の行程がある日数はSheet2のB7セルに、2024年2月の行程がある日数はSheet2のAG7セルに表示させるものとする)

これらの仮定に基づいた場合、Sheet2のB7セルに入力する関数の一例は次のようなものになります。

=IF(ISERROR(1/(INDEX($B$2:B$2,MATCH("*?",$B$2:B$2,-1))&INDEX($B$3:B$3,MATCH("*?",$B$3:B$3,-1))&"1日")),"",SUMPRODUCT((COUNTIFS(Sheet1!$B$3:$Z$3,"開始",Sheet1!$C$3:$AA$3,"終了",Sheet1!$B$4:$Z$4,"<="&INDEX($B$2:B$2,MATCH("*?",$B$2:B$2,-1))&INDEX($B$3:B$3,MATCH("*?",$B$3:B$3,-1))&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}&"日",Sheet1!$C$4:$AA$4,">="&INDEX($B$2:B$2,MATCH("*?",$B$2:B$2,-1))&INDEX($B$3:B$3,MATCH("*?",$B$3:B$3,-1))&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}&"日")>0)*ISNUMBER((INDEX($B$2:B$2,MATCH("*?",$B$2:B$2,-1))&INDEX($B$3:B$3,MATCH("*?",$B$3:B$3,-1))&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}&"日")+0)))

　Sheet2のB7セルに上記の関数を入力した後は、Sheet2のB7セルをコピーしてSheet2のAG7セルなどの他の月の行程日数を表示させるセルに貼り付けて下さい。

以上です。

回答No.7

msMike

参考までに、確認させてください。
》 1月のセルに16日と6日を足した21日と表示したい。
なぜ22日でなく、21日なのですか？

回答No.6

kkkkkm

回答No.2の訂正です。

説明がおかしかったので訂正です。

> 2行目と6行目は表示形式で同じ見た目にしてください。

6行目は数値で表示されますので、2行目はただの数値で1、2、3、4、5・・・として

「月」を表示する場合は
2行目と6行目を見た目同じにするのでしたら両方とも
表示形式の
ユーザー定義で
# "月"
とか
G/標準 "月"
にしてください。

回答No.5

imogasi

小生が考え違いをしていることもあるので、その際はご免なさい。
＊＊質問の意図が判り難い。状況の表現が足りない。
＊＊下記のようなことを聞いているのではないか？それを確認するのがし目的です。
＊＊すると既出の回答は、それでよいのか？
ーー
こういう質問者の職場の仕事をストレートに例として質問されると、読者・部外者には、全貌が判らないので、判り難いことが多い。
それだけに例の挙げ方や、説明の仕方に一段の工夫（詳しい説明）が必要と思う。
実例を挙げる際に、データの選定を誤ると、全体の多様なケースの1部を漏らして、解釈される恐れがある。
ーー
当方の勝手な、例で説明します。
本件　A2:E7
開始日 終了日 全日数 当月 翌月
1月5日 1月20日 16 16 0
1月25日 1月26日 2 2 0
1月28日 1月30日 3 3 0
1月30日 2月5日 7 2 5
2月15日 3月28日 42 14 28
3月20日 3月31日 12 12 0
　C2の式　=B2-A2+1
　D2の式　=IF(MONTH(A2)=MONTH(B2),C2,EOMONTH(A2,0)-A2+1)
　E2の式　=C2-D2
この式は、うるう年や月末の大小は、自ずと反映される（折り込み済み）。
年越しも反映される。でも表データに年の表示は必要だろう。
当月と翌月しか跨らない。（３か月跨りのデータはないと、仮定）！！！
＝＝
このD列、E列から、例えば「１月の該当日数の和を関数で出す」というのが、質問の意図ではないか。
質問の画像では、A列データは横１列に出てくる。
その回答式は、ちょっと考えたが難しくて、１つの式などの簡単な式で、回答がすぐ浮かばない。

回答No.4

kkkkkm

No.2の訂正です。

=SUMPRODUCT(($A$6:$L$6=A2)*($A$7:$L$7))
は
=SUMIF($A$6:$L$6,A2,$A$7:$L$7)
でも可能でした。

SUMPRODUCT好きなので頭がそっちにいってました。

回答No.3

kkkkkm

> 2月はうるう年なので14日と表示　3月のセルには29日と表示したい。

見逃してました。以下に変更してください。

A7に
=IF(MONTH(A10)<>MONTH(B10),DATEDIF(A10,EOMONTH(A10,0),"d"),DATEDIF(A10,B10,"d")+1)
B7に
=IF(MONTH(G10)<>MONTH(H10),DATEDIF(EOMONTH(G10,0),H10,"d")+1,0)

回答No.1

yamada82

自分ならこんな感じで作ります。
作業日をmonth関数で月表示させて、それをcountif関数で各月の合計日数を集計します。テーブル機能も使えば範囲も勝手に広がってくれます。