Excel,Spreadsheetで営業日を入力する関数の一覧（第〇営業日、第〇週）

「第一営業日、第〇営業日と入れるところを全部手書きでやってるのがめんどくさい」

「関数化したいけど、コピペしてすぐ使えるようにしてほしい」

今回は、Google Spread Sheet 、Excelで、営業日や第〇週営業日などを出力できる関数を紹介します。

網羅的に、極力そのまま使えるようにして、書いてありますので、ぜひ使ってみてください。

※必読【前準備】年と月を入力するセルを準備する

適当なセルに、年と月を入力してください。

以降ではこの二つのセルを使います。

"年セル" "月セル"　と関数の中には書いていくので、実際に使う際は、そこを置き換えていただければと思います。

第〇営業日を表示する関数

第１営業日

=WORKDAY(DATE("年セル" , "月セル" , 0),1,"祝日セル")

第２営業日

=WORKDAY(DATE("年セル" , "月セル" , 0),2,"祝日セル")

第３営業日

=WORKDAY(DATE("年セル" , "月セル" , 0),3,"祝日セル")

第４営業日

=WORKDAY(DATE("年セル" , "月セル" , 0),4,"祝日セル")

第５営業日

=WORKDAY(DATE("年セル" , "月セル" , 0),5,"祝日セル")

第５営業日以降

=WORKDAY(DATE("年セル" , "月セル" , 0),〇,"祝日セル")

〇の部分を表示したい営業日にしてください。

第〇週の〇曜日の日付を表示する関数

第１週の〇曜日

第1週の月曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>2,(7-(WEEKDAY(DATE("年セル","月セル",1))-2))+DATE("年セル","月セル",1),2-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))

第1週の火曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>3,(7-(WEEKDAY(DATE("年セル","月セル",1))-3))+DATE("年セル","月セル",1),3-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))

第1週の水曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>4,(7-(WEEKDAY(DATE("年セル","月セル",1))-4))+DATE("年セル","月セル",1),4-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))

第1週の木曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>5,(7-(WEEKDAY(DATE("年セル","月セル",1))-5))+DATE("年セル","月セル",1),5-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))

第1週の金曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>6,(7-(WEEKDAY(DATE("年セル","月セル",1))-6))+DATE("年セル","月セル",1),6-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))

第1週の土曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>7,(7-(WEEKDAY(DATE("年セル","月セル",1))-7))+DATE("年セル","月セル",1),7-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))

第1週の日曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>8,(7-(WEEKDAY(DATE("年セル","月セル",1))-8))+DATE("年セル","月セル",1),8-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))

第2週の〇曜日

第2週の月曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>2,(7-(WEEKDAY(DATE("年セル","月セル",1))-2))+DATE("年セル","月セル",1),2-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+7

第2週の火曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>3,(7-(WEEKDAY(DATE("年セル","月セル",1))-3))+DATE("年セル","月セル",1),3-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+7

第2週の水曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>4,(7-(WEEKDAY(DATE("年セル","月セル",1))-4))+DATE("年セル","月セル",1),4-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+7

第2週の木曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>5,(7-(WEEKDAY(DATE("年セル","月セル",1))-5))+DATE("年セル","月セル",1),5-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+7

第2週の金曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>6,(7-(WEEKDAY(DATE("年セル","月セル",1))-6))+DATE("年セル","月セル",1),6-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+7

第2週の土曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>7,(7-(WEEKDAY(DATE("年セル","月セル",1))-7))+DATE("年セル","月セル",1),7-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+7

第2週の日曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>8,(7-(WEEKDAY(DATE("年セル","月セル",1))-8))+DATE("年セル","月セル",1),8-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+7

第3週の〇曜日

第3週の月曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>2,(7-(WEEKDAY(DATE("年セル","月セル",1))-2))+DATE("年セル","月セル",1),2-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+14

第3週の火曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>3,(7-(WEEKDAY(DATE("年セル","月セル",1))-3))+DATE("年セル","月セル",1),3-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+14

第3週の水曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>4,(7-(WEEKDAY(DATE("年セル","月セル",1))-4))+DATE("年セル","月セル",1),4-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+14

第3週の木曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>5,(7-(WEEKDAY(DATE("年セル","月セル",1))-5))+DATE("年セル","月セル",1),5-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+14

第3週の金曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>6,(7-(WEEKDAY(DATE("年セル","月セル",1))-6))+DATE("年セル","月セル",1),6-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+14

第3週の土曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>7,(7-(WEEKDAY(DATE("年セル","月セル",1))-7))+DATE("年セル","月セル",1),7-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+14

第3週の日曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>8,(7-(WEEKDAY(DATE("年セル","月セル",1))-8))+DATE("年セル","月セル",1),8-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+14

第4週の〇曜日

第4週の月曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>2,(7-(WEEKDAY(DATE("年セル","月セル",1))-2))+DATE("年セル","月セル",1),2-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+21

第4週の火曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>3,(7-(WEEKDAY(DATE("年セル","月セル",1))-3))+DATE("年セル","月セル",1),3-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+21

第4週の水曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>4,(7-(WEEKDAY(DATE("年セル","月セル",1))-4))+DATE("年セル","月セル",1),4-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+21

第4週の木曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>5,(7-(WEEKDAY(DATE("年セル","月セル",1))-5))+DATE("年セル","月セル",1),5-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+21

第4週の金曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>6,(7-(WEEKDAY(DATE("年セル","月セル",1))-6))+DATE("年セル","月セル",1),6-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+21

第4週の土曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>7,(7-(WEEKDAY(DATE("年セル","月セル",1))-7))+DATE("年セル","月セル",1),7-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+21

第4週の日曜日

=IF(WEEKDAY(DATE("年セル","月セル",1))>8,(7-(WEEKDAY(DATE("年セル","月セル",1))-8))+DATE("年セル","月セル",1),8-WEEKDAY(DATE("年セル","月セル",1))+DATE("年セル","月セル",1))+21

〇日（指定した日付）を表示する関数

=DATE("年セル","月セル",0)+〇

〇に指定した日付を入れてください。

次月1日より1週間前

=DATE("年セル","月セル",0)-6

月の最終日

=DATE("年セル","月セル",0)

月の最終営業日

=IF(WEEKDAY(EOMONTH(DATE("年セル","月セル"+1,0),0),2)=7,EOMONTH(DATE("年セル","月セル"+1,0),0)-2,IF(WEEKDAY(EOMONTH(DATE("年セル","月セル"+1,0),0),2)=6,EOMONTH(DATE("年セル","月セル"+1,0),0)-1,EOMONTH(DATE("年セル","月セル"+1,0),0)))

まとめ

月のタスクをまとめているスプレッドシートがあったのですが、今まで手入力をしていたようでした。今回そこで使えた営業日の関数をより便利に使ってもらえたらいいなと思い、記事にしています。あなたの参考になればうれしいです。