Excelでデータを扱う上で、日付は切っても切り離せない重要な要素です。
今回は日付に関するTipsをご紹介していきます。
1．月末の日付の出し方
2．○日後の平日の出し方
3．日付かどうか判定する方法
1．月末の日付の出し方
月末の日付は、年・月によって31日だったり30日だったり29日だったり…と違いがあります。
そういう時に、自動で簡単に月末の日付を出す方法をご紹介します。
◆DATE関数の第三引数を0にする
例えば、2021年1月31日 という日付を出したいとき、
DATE関数でシリアル値を作ると、以下のようになります。
(固定値で日付を入力する場合)
=DATE(2021,1,31)
月によって、31や30と打ち換えるのは面倒です。
そこで、月末日はどういう日付なのかを考えてみます。
月末日は「翌月1日の前日」と言い換えることができます。
文章で見ると当たり前のことなのですが、これを関数で再現すれば、
その月が何日間であろうと簡単に月末の日付を出すことができます。
=DATE(2021,2,1)-1
さらに進んで、実はDATE関数の第二引数(MONTH)と第三引数(DAY)は、
1未満の数字、つまり0や負の値(マイナス値)を指定することもできます。
そこで、第三引数を 1-1 の0とすることで、月末の日付を出すことができます。
=DATE(2021,2,0)
DATE関数で第三引数を0にすると、前月の末日の日付が出せる、ということです。
◆TODAY関数と組み合わせて、当月の月末日を常に表示する
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
2. ○日後の平日の出し方
所定の営業日数後の日付を出すには、WORKDAY関数を使います。
※別途祝日マスタが必要です。サンプルでは、名前の定義をした祝日マスタを使用しています。
例えば、5営業日後なら以下の式になります
=WORKDAY(日付,5,祝日マスタ)
5営業日後ではなく、5日後の平日(5日後が土日祝の場合はその次の平日)を出したい場合には、どうすればよいでしょうか？
5日後の日付が土だったら2を足して、日曜日だったら1を足して…と考えるととても大変です。
どんな日付を求めたいかを考えるときに「4日後の、さらに1営業日後の日付」と変換することで、
簡単に5日後の平日の日付を求めることができます。
=WORKDAY(日付+4,1,祝日マスタ)
◆お給料の振込日(毎月25日払いで、25日が土日祝の場合はその前の平日)を出す
→ 26日の1営業日前を出す
=WORKDAY(DATE(2021,4,26),-1,祝日マスタ)
3．日付かどうか判定する方法
「所定の列に日付を入力したら、その行に色を付けたい」というご相談をいただくことがあります。
条件付き書式を使用して設定をすればよいのですが、
ネックになるのは「入力された値が日付かどうか」ということ。
実は、Excelのワークシート関数では、セルの値が日付かどうかを判定する関数は
残念ながらありません。
(余談ですが、VBA関数にはISDATEがあります）
そのような場合では、日付の幅を設定してその範囲内であれば
日付だと判断する、という方法をとるのがよいでしょう。
◆E列が所定の日付(2020/1/1～2021/12/31)の時に、行全体を塗りつぶす場合
・条件付き書式で以下の数式を入力
=AND($E2>=DATE(2020,1,1),$E2<=DATE(2021,12,31))
そもそもとして考えると、日付が入っているかどうかを判定しなければいけない表というのが
効率的とは言えません。
データベース形式でデータを入力していくのであれば、
同じ列には、同じ性質のデータを入力するのが望ましいです。
日付と文字列の両方を入力するのではなく、別途列を設けて、日付を入力する列、備考など文字を入力する列とする方がデータを利用するときにも使い勝手がよくなります。
それにより、条件は○列が空白以外、という簡単なもので済ますこともできます。
作表の際の参考にしてください。
今回は日付に関するTipsをご紹介しました！
