スプレッドシートで色々な日付を自動算出する早見表【サンプル付き】
レポート制作の強い味方になってくれるスプレッドシート（Excel・Googleスプレッドシート）。
毎週の進捗レポートや毎月の報告レポートなど、定例で出すべき資料がたくさんあるので、
「ここの日付が間違ってる」
「ここの参照期間が間違っている」
など、日付に関する修正やチェックが大変だという方もいるのではないでしょうか？
一つ一つの手動修正は時間がかからなくても、積もり積もると運用時間がかかったり、運用手順書が膨大になったり、引き継ぎのタイミングで漏れてしまったり、管理やチェックする側も大変だったり…と、何かと手間がかかってしまいますよね。
この面倒な日付変更の作業は、ちょっとした式の組み合わせで自動化できます。自動化することで細かい作業が減って、チェックも簡単になるので、今日から日付表記は自動化しちゃいましょう！
早見表スプレッドシートサンプル
実際の式の動きが見られるGoogleスプレッドシート早見表はこちらです。「ファイル」＞「コピーを作成」にてコピーしてご活用ください。
日付自動算出式の早見表
WEEKDAY関数、EOMONTH関数、EDATE関数を用いることで、ほとんどの日付は表現することができます。
当日を起点にする場合はTODAY関数を使いますが、任意の日付に置き換えたり、日付が書かれたセルを参照することもできます。DATE関数、YEAR関数、MONTH関数、DAY関数も日付の表現に便利なので、ぜひ活用してみてください。
使用した関数の説明は、早見表の後で紹介します。
| 欲しい日付 | 当日を起点に欲しい日付を自動算出する式 |
|---|---|
| 当日 | =TODAY() |
| 前日 | =TODAY()-1 |
| 当週 日曜日 | =TODAY()-WEEKDAY(TODAY(),2) |
| 当週 土曜日 | =TODAY()-WEEKDAY(TODAY(),2)+6 |
| 前週 日曜日 | =TODAY()-WEEKDAY(TODAY(),2)-7 |
| 前週 土曜日 | =TODAY()-WEEKDAY(TODAY(),2)-1 |
| 当月 月初 | =EOMONTH(TODAY(),-1)+1 |
| 当月 月末 | =EOMONTH(TODAY(),0) |
| 前月 月初 | =EOMONTH(TODAY(),-2)+1 |
| 前月 月末 | =EOMONTH(TODAY(),-1) |
| 当月 第一月曜日 | =EOMONTH(TODAY(),-1)+8-WEEKDAY(EOMONTH(TODAY(),-1),2) |
|
当月 第一営業日 （※1） |
=IF(WEEKDAY(EOMONTH(TODAY(),-1)+1,2)=7,EOMONTH(TODAY(),-1)+2,IF(WEEKDAY(EOMONTH(TODAY(),-1)+1,2)=6,EOMONTH(TODAY(),-1)+3,EOMONTH(TODAY(),-1)+1)) |
|
当月 最終営業日 （※1） |
=IF(WEEKDAY(EOMONTH(TODAY(),0),2)=7,EOMONTH(TODAY(),0)-2,IF(WEEKDAY(EOMONTH(TODAY(),0),2)=6,EOMONTH(TODAY(),0)-1,EOMONTH(TODAY(),0))) |
|
当Q（四半期） （※2） |
=ROUNDUP(MONTH(EDATE(TODAY(),-3))/3)&”Q” |
|
前Q（四半期） （※2） |
=ROUNDUP(MONTH(EDATE(TODAY(),-6))/3)&”Q” |
|
当Q 開始日 （※2） |
=DATE(YEAR(TODAY()),(ROUNDUP(MONTH(TODAY())/3)-1)*3+1,1) |
|
当Q 終了日 （※2） |
=EOMONTH(DATE(YEAR(TODAY()),(ROUNDUP(MONTH(TODAY())/3,0)-1)*3+1,1),2) |
|
前Q 開始日 （※2） |
=DATE(YEAR(EDATE(TODAY(),-3)),(ROUNDUP(MONTH(EDATE(TODAY(),-3))/3,0)-1)*3+1,1) |
|
前Q 終了日 （※2） |
=EOMONTH(DATE(YEAR(EDATE(TODAY(),-3)),(ROUNDUP(MONTH(EDATE(TODAY(),-3))/3,0)-1)*3+1,1),2) |
|
当半期 H表記 （※2） |
=ROUNDUP(MONTH(EDATE(TODAY(),-3))/6,0)&”H” |
|
当半期 上下表記 （※2） |
=IF(MONTH(EDATE(TODAY(),-3))<7,”上期”,”下期”) |
|
前半期 H表記 （※2） |
=ROUNDUP(MONTH(EDATE(TODAY(),-9))/6,0)&”H” |
|
前半期 上下表記 （※2） |
=IF(MONTH(EDATE(TODAY(),-9))<7,”上期”,”下期”) |
|
当半期 開始日 （※2） |
=DATE(YEAR(EDATE(TODAY(),-3)),(ROUNDUP(MONTH(EDATE(TODAY(),-3))/6,0)-1)*6+4,1) |
|
当半期 終了日 （※2） |
=EOMONTH(DATE(YEAR(EDATE(TODAY(),-3)),(ROUNDUP(MONTH(EDATE(TODAY(),-3))/6,0)-1)*6+4,1),5) |
|
前半期 開始日 （※2） |
=DATE(YEAR(EDATE(TODAY(),-9)),(ROUNDUP(MONTH(EDATE(TODAY(),-9))/6,0)-1)*6+4,1) |
|
前半期 開始日 （※2） |
=EOMONTH(DATE(YEAR(EDATE(TODAY(),-9)),(ROUNDUP(MONTH(EDATE(TODAY(),-9))/6,0)-1)*6+4,1),5) |
|
該当期 （※3） |
=YEAR(EDATE(TODAY(),-3))-2021&”期” |
| 当年 年初日 | =DATE(YEAR(TODAY()),1,1) |
| 当年 年末日 | =DATE(YEAR(TODAY()),12,31) |
| 前年 年初日 | =DATE(YEAR(TODAY())-1,1,1) |
| 前年 年末日 | =DATE(YEAR(TODAY())-1,12,31) |
| 前年同日 | =EDATE(TODAY(),-12) |
| 前年同週日 | =TODAY()-364 |
※1：土日休みのみに対応した式になります。祝日や平日休みの場合は自動算出できません。
※2：年度始まりが4月の想定の式になります。年度始まりが4月以外の場合は、EDATE関数の第二引数の値を調整してください。
※3：2022年4月が創立の想定の式になります。創立年を変えたい場合は、「2021」の部分を調整してください。
使用している関数の紹介
日付に関する関数は以下の公式ドキュメントでも確認することができます。
今回は早見表の式で使用した関数についてピックアップして紹介します。
TODAY関数
現在の日付を返す関数です。当日を起点に計算をしたい場合に便利です。取得した日付は加算や減算することができます。
| 書式・構文 | =TODAY() |
WEEKDAY関数
指定した日付の曜日を数字で返す関数です。特定の曜日を表したいときに便利です。種類は何曜日を先頭にするかを指定することができます。
| 書式・構文 | =WEEKDAY(日付, 種類) |
- 種類が 1 の場合、日曜日の値が 1 で、土曜日の値が 7 となります。
- 種類が 2 の場合、月曜日の値が 1 で、日曜日の値は 7 となります。
- 種類が 3 の場合、月曜日の値が 0 で、日曜日の値は 6 となります。
EOMONTH関数
現在の日付を返す関数です。当日を起点に計算をしたい場合に便利です。
取得した日付は加算や減算することができます。
| 書式・構文 | =EOMONTH(開始日, 月数) |
- 月数が 0 の場合、開始日の月の末日になります。
- 月数が 1 の場合、開始日から1ヶ月後の月の末日になります。
- 月数が -1 の場合、開始日から1ヶ月前の月の末日になります。
EDATE関数
Expiration DATEの略。指定した開始日を基準に、指定した月数だけ後の日付を求めることができます。
正確な○ヶ月後の算出が可能で、例えば一年後や一年前の日付を出そうとした際、365を加算・減算するとうるう年対応ができませんが、EDATE関数であれば対応することが可能です。
| 書式・構文 | =EDATE(開始日, 月数) |
- 月数が 0 の場合、開始日の日付になります。
- 月数が 1 の場合、開始日からちょうど1ヶ月後の日付になります。
- 月数が -1 の場合、開始日からちょうど1ヶ月前の日付になります。
DATE関数
DATE 関数は、「年」「月」「日」を引数にして日付データを作成することができます。
テキスト形式になってしまってる日付を、日付形式（シリアル値）に変換することができ、計算式に組み込むことができます。
また、後述のYEAR関数、MONTH関数、DAY関数と組み合わせて使うことで、「任意の日付を起点に年・月・日のいずれかを計算したい」などの処理も行うことができます。
| 書式・構文 | =DATE(年, 月, 日) |
YEAR関数
日付（シリアル値）から「年」に当たる数値を取り出します。
| 書式・構文 | =YEAR(日付) |
MONTH関数
日付（シリアル値）から「月」に当たる数値を取り出します。
| 書式・構文 | =MONTH(日付) |
DAY関数
日付（シリアル値）から「日」に当たる数値を取り出します。
| 書式・構文 | =DAY(日付) |
ROUNDUP関数
指定した桁数で切り上げることができる関数です。桁数を省略すると小数点以下を切り上げた整数にすることができます。
四半期や半期の計算など、複数の月をある期間にまとめる際に活用できます。
| 書式・構文 | =ROUNDUP(数値, 桁数) |
- 桁数が 0 または省略の場合、小数点第一位を切り上げした数値になります。
- 桁数が 1 の場合、小数点第二位を切り上げた数値になります。
- 桁数が -1 の場合、1の位を切り上げた数値になります。
まとめ
上記の日付算出式とVLOOKUP関数、SUMIF関数、SUMPRODUCT関数など組み合わせれば、
「前週の問い合わせ件数の合計値を自動で出す」
「前月の売上を自動で参照する」
など、自動化の応用は無限大です。
レポートのグラフや表に関する作業はできるだけ自動化させて、自由に使える時間をどんどん増やしていきたいですね。
