運用で使えるExcel日付自動入力
はじめに
業務でExcelを使用していると、日付に関する計算や自動入力が必要になることが多々あります。
特に以下のようなケースでは、毎回手動で日付を入力するのは非効率的です：
- 月次レポートの作成時の対象期間入力
- 四半期報告における期首・期末の設定
- 年度処理における半期データの集計
- 週次進捗管理における日付更新
このような定型業務を効率化するため、Excel関数を使用した日付の自動計算方法をまとめました。
※ご紹介する関数の特徴としては営業日を作業日とするために休日が作業日に該当する場合は翌営業日もしくは前営業日にスライドする仕様となっています
事前準備
休日リストの作成
日付計算で祝日を考慮するために、別シートに休日リストを作成する必要があります[1]。
- 新規シートを作成し、「休日」などの分かりやすい名前を付ける
- 以下の列を作成:
- 年月日
- 休日名称
半期リストの作成
年度半期の計算のために、別シートに半期の区切りを定義したリストを作成します。
- 新規シートを作成（休日リストと同じシートでも可）
- 以下の列を作成:
- 半期リスト
- 半期リスト
テーブル化の手順
- リストの範囲を選択
- 「ホーム」タブ→「テーブルとして書式設定」をクリック
- 「先頭行をテーブルの見出しとして使用する」にチェック
- テーブル名を設定（例：「休日リスト」「半期リスト」）
使用する主な関数
本記事で紹介する日付計算では、主に以下の関数を使用します：
- DATE関数：年月日から日付を生成
- EOMONTH関数：月末日を取得
- WEEKDAY関数：曜日を判定
- TODAY関数：現在の日付を取得
それでは、頻度別に具体的な関数の使い方を見ていきましょう。
年次処理
年単位の処理で使用する日付計算です。
=LET(targetdate,$C$2,targetmonth,4,rundate,DATE(YEAR(EDATE(targetdate-1,-targetmonth))+1,targetmonth,1)-1,thisyeardate,WORKDAY(rundate,1,holiday[年月日]),IF(targetdate>thisyeardate,WORKDAY(EDATE(rundate,12),1,holiday[年月日]),thisyeardate))
年度半期処理
4月始まりの年度における上期（4月～9月）・下期（10月～3月）の初日と末日を計算します。
半期初日
=WORKDAY(MIN(IF(halfyear[半期リスト]>=$C$2,halfyear[半期リスト]))-1,1,holiday[年月日])
半期末日
=WORKDAY(MIN(IF(halfyear[半期リスト]>=$C$2,halfyear[半期リスト])),-1,holiday[年月日])
四半期処理
年度における四半期の初日と末日を自動計算します。
第1四半期（4-6月）から第4四半期（1-3月）までを自動判定します。
四半期初日
=LET(targetdate,$C$2,thisworkdate,WORKDAY(DATE(YEAR(targetdate),FLOOR.MATH(MONTH(targetdate),3)+1,1)-1,1,holiday[年月日]),IF(targetdate<=thisworkdate,thisworkdate,WORKDAY(DATE(YEAR(targetdate),CEILING(MONTH(targetdate),3)+1,1)-1,1,holiday[年月日])))
四半期末日
=WORKDAY(DATE(YEAR($C$2),CEILING(MONTH($C$2), 3)+1,0)+1,-1,holiday[年月日])
月次処理
月単位の処理で使用する日付計算です。
月初・月末・中間日（15日）の自動計算に対応します。
月初日
=LET(targetdate,$C$2,thismonthdate,WORKDAY(EOMONTH(targetdate,-1),1,holiday[年月日]),IF(targetdate>thismonthdate,WORKDAY(EOMONTH(targetdate,0)+1,1,holiday[年月日]),thismonthdate))
月末日
=WORKDAY(EOMONTH($C$2,0)+1,-1, holiday[年月日])
中間日（15日）
=LET(targetdate,$C$2,targetday,15,thismonthdate,DATE(YEAR(targetdate),MONTH(targetdate),targetday),IF(targetdate>thismonthdate,DATE(YEAR(targetdate),MONTH(targetdate)+1,targetday),thismonthdate))
週次処理
週単位の処理で使用する日付計算です。
週初め（月曜日）や週末（金曜日）の自動計算が可能です。
=LET(targetdate,$C$2,thisweekdate,WORKDAY(targetdate-WEEKDAY(targetdate,3)-1,1,holiday[年月日]),IF(targetdate>thisweekdate,WORKDAY(targetdate-WEEKDAY(targetdate,3)+6,1,holiday[年月日]),thisweekdate))
補足
- 日付計算では、DATE関数とEOMONTH関数を組み合わせることで柔軟な日付指定が可能です
- 曜日の判定にはWEEKDAY関数が便利です
- 日付のフォーマット指定は「ユーザー定義」で細かく設定できます
- これらの関数は数式を組み合わせることで、より複雑な条件にも対応できます
まとめ
本記事で紹介した関数を活用することで、日付に関する定型業務を大幅に効率化できます。
特に定期的なレポート作成やデータ更新作業では、手作業による入力ミスも防ぐことができます。
ぜひ、実際の業務に合わせてカスタマイズしてご活用ください。