毎週週頭を月曜として 日付を自動的に入力したいです

質問

いつもお世話になっています。

データを毎週集計しています。

毎週週頭を月曜として、日付を自動的に入力したいです。

B2に当月の日付を"ctrl+れ"を押すと当月の最初の人最後の日を自動的に入力する数式はわかったのです。
しかし、各週の最初の日と最後の日が自動表示する関数がわかりません。

御指導よろしくお願いします。

回答No.4

bunjii · ベストアンサー

>B2に当月の日付を"ctrl+れ"を押すと当月の最初の人最後の日を自動的に入力する数式はわかったのです。
先ず、質問文の字句の訂正をさせて頂きます。
「B2に当月の日付」→「B1に今日の日付」、「ctrl+れ」→「ctrl+;」、「当月の最初の人」→「B2に当月の初日と」、「最後の日」→「D2に当月の末日」、「自動的に入力する数式」→「代入する数式」
当月の最初の日は1日なので悩むことは無いでしょう。
しかし、提示の数式以外にも次の数式が使えます。
B2=EOMONTH(B1,-1)+1
当月の最後の日とは当月の末日と言い換えた方が良いと思います。
D2=EOMONTH(B1,0) 提示の数式で良いでしょう。

>しかし、各週の最初の日と最後の日が自動表示する関数がわかりません。
第1週目の月曜日は前月の日付で良いか？、それとも空欄にするか？
当月の末日を含む週の日曜日が翌月になるときは前述のように翌月の日付にするか？、空欄にるすか？、の指定が必要です。
1つの関数ではできませんので、幾つかの関数を組み合わせた数式を組み立てます。
基本的には第1週の日付と第2週以降の日付の算出方法が異なります。
第1週の月曜日が前月の日付になることを認める場合は次の数式で良いでしょう。
B3=B2-WEEKDAY(B2,2)+1
第1週の日曜日は単純な加算で求められます。
D2=B3+6
第2週の月曜日は次の数式が適当と思います。
B4=IF(B3="","",IF(B3+6<EOMONTH($B$1,0),B3+7,""))
第2週の日曜日は次の数式で良いでしょう。
D4=IF(D3="","",IF(D3<EOMONTH($B$1,0),D3+7,""))
B4とD4セルを下へ8行目（第6週）までコピーすれば完成です。
Excel 2013で検証した結果が添付画像です。

お礼

bunjiiさん
いつもありがとうございます。
遅くなりました。
動作確認できました。
今後、使用させていただきます。
ありがとうございます！

回答No.7

tsubu-yuki

まず・・Ｎｏ２殿。
直近では来月、２０１６年１０月が第６週が発生するケースに当たりませんか。
と、煽り合いをしてもしょうがないので本題に。

添付ファイルの通り、作ってみました。
式はいろいろ出ていますので、とりあえず参考までにどうぞ。
各セルには以下。
　Ｂ２セル：=DATE(YEAR(B1),MONTH(B1),1)
　Ｄ２セル：=DATE(YEAR(B1),MONTH(B1)+1,0)
　　　　　　※翌月の０日目、との指定で、当月の末日を出します。
　　　　　　　閏年にも対応します。
　Ｂ３セル：=B2-WEEKDAY(B2,3)
　　　　　　※第２引数に３を指定すると、月曜を０として計算します。
　　　　　　　なので、その日（Ｂ２）の曜日を引いてやれば、月曜日が出てきます。
　Ｄ３セル：=B3+6　　※６日後
　Ｂ４セル：=B3+7　　※７日後
　以下省略。
そんな感じです。

問題の６週目ですが、私なら条件付き書式を仕込みます。
つまり、８行目に
「Ｂ１セルの『月』と、Ｂ８セルの『月』が違ったら」
という条件を付けて、文字色を背景色に合わせています。※白です。

こんな感じでもできますよ。

お礼

ちょっと条件付き設定に慣れておらず、２時間ほど苦戦しましたが、動作確認できました。
ありがとうございます。

回答No.6

Chiquilin

すみません。曜日を間違えました。

第1週目の初日(月曜)：=$B$2-WEEKDAY($B$2-2)

ですね。

当日初日は「=B1-DAY(B1)+1」でもいいです。

お礼

ありがとうございます。

回答No.5

Chiquilin

第1週目の初日：=$B$2-WEEKDAY($B$2-1)+7

これが分かれば 後は全部足し算なのですから考えるまでもないでしょう。

ついでに。少し不細工だと思ったので
当日の初日：=EOMONTH(B1,-1)+1
当月最終日：=EOMONTH(B1,0)

お礼

先月分に＋１を加えると当日初日が求められるということですね。
ありがとうございます。

回答No.3

msMike

》 月の初めが日曜日だった場合、…
昨年の11月、今年の５月、来年の１月、10月が正に該当しますね。
果たして貴方の「第六週目と計算できる」が的を射ているかカレンダーを見て！

お礼

ありがとうございます

回答No.2

msMike

》 各週の最初の日と最後の日が自動表示する関数
最初の日は「週頭を月曜」の記述から分かるけど、「最後の日」は日曜でＯＫですよね？まさか、金曜とかじゃなくて、祝日も関係ないですよね？
後になって「そうじゃなかった」なんて嫌だから確認しています。

》 B2に当月の日付を
なんて高らかに謳っていますけど、添付図では B1 になってるのはこれ如何に？
ついでながら、添付図の６行目に「第６週目」なんてシャーシャーと入力してますが、何月のことですか？
後で「あれは単なるサンプルだった」なんて、ほざかないでぇ～！

お礼

ありがとうございます

補足

説明不足ですみません。

・最後の日は日曜日です。

・B1に当月の日付を入力するとB2に月の最初の日が表示されます。すみません、おっしゃっているように間違えました。

・第六週はあり得ると思います。
７×４で２８、月の最高日数が３１です。
としまして、仮に月曜日が週の始まりだとしますと、月の初めが日曜日だった場合、その日曜日が一週目で、２日目が月曜日としますと、５週いっぱいまでに29日として、残りの２日を第六週目と計算できると考えたのですが。
これは私の考えです。
このとおりでない週の第何週の計測でも構いませんので、アドバイスくだだい。
よろしくお願い致します。

回答No.1

mshr1962

まあ基本として、ハッピーマンデーの祝日の計算と同じなので、下記が参考になります。
・エクセル技道場 関数35.成人の日/体育の日
http://www2.odn.ne.jp/excel/waza/function.html#SEC35

補足

第n曜日を求める　が使えそうです。
明日、検証します。
ありがとうございます。