Excel(エクセル)で決まった支払日や期限日を計算する方法|10日締めや20日締めを設定しよう

スポンサーリンク
スポンサーリンク

Introduction

Excel(エクセル)で、支払日や期限日などの特定の日付を表示する方法です。

今回は2つの方法を紹介します。

  • EOMONTH関数を使用する方法
  • DATE、YEAR、MONTH、DAY関数を使用する方法

EOMONTH関数を使用する方法が簡単かもしれません。

 

 毎月同じ日を設定したいにゃー
スポンサーリンク

EOMONTH関数を使用して日付を表示するやり方

下の表で、「日付」のセルの翌月20日を表示してみます。

使用する関数はEOMONTH関数。


EOMONTH関数・・・開始日から、指定した数に対した前後の月の最終日を表示する(シリアル値を返す)

EOMONTH(開始日,月)

  • 開始日・・・期間の最初の日付(開始日)を指定
  • 月・・・[開始日]からの月数を指定
    • 正の数・・・[開始日]より後の日付を返す
    • 負の数・・・[開始日]より前の日付を返す

この関数の詳しい使用法は、Excel(エクセル)で月末の営業日、請求書、期限日や満期日を表示する|EOMONTH関数にあるので興味のある方はご覧ください。

 

考え方としては、その月の末日を算出して、そこから目的の日数を足します。

まず、下の図のようにEOMONTHの引数を入力します。

[開始日]には、基準となる日付の場所。

[月]には、今回は基準日の末日が欲しいので「0」を入力です。


もし、下の表のように、日付を後でオートフィル機能を使用して下にコピーする予定であれば、[開始日]の場所は絶対参照にします。


結果は下のように、[開始日]で設定した月の末日が表示されます

あとは「20日」にすればOKですね。

[数式バー]の今作成した式の最後に「+20」を入力すれば完成です。

=EOMONTH(E2,0)+20

ちゃんと翌月の20日になりました。

条件によって、日付を変更する

常に同じ日を設定するのであれば、これで大丈夫なのですが、「10日までは、その月で設定して、10日以降は翌月にしたい」場合はどうしましょう。

そんな時はIF関数を使用します。

下のように長い式になります。

=IF(DAY(E2)<=10,EOMONTH(E2,-1)+20,EOMONTH(E2,0)+20)

式の途中で、DAY関数を使用します。


DAY関数・・・日付の日情報を取り出す(シリアル値を返す)

DAY(シリアル値)

  • シリアル値・・・日にちを取り出したいセルを指定

※シリアル値とは、日付を「1900/1/1」を「1」とした数値のことです。エクセルは日付を、このシリアル値で考えています。

シリアル値に関しては、Excel(エクセル)での日付の扱い方|日付の表示を変更する方法をご覧ください。

 

式だけ抜き出したのが下の画像。

内容を言葉で説明すると、

「もし、E2の日付が10日以前だったら一か月前の末日の20後にする。違う場合は、その月の末日の20日後にする。」

という感じ。

IF関数を組み合わせることで、様々な設定ができそうですね。

DATE関数を使用して日付を表示するやり方

では、次にDATE関数を使用する方法です。これまでと同じく、20日の設定にしたいと思います。

表は先ほどと同じものを使用します。

使用する関数は、DATE関数とYEAR関数、MONTH関数、DAY関数。


YEAR・MONTH・DAY関数・・・年・月・日の情報を取り出す(シリアル値を返す)

YEAR(シリアル値)

MONTH(シリアル値)

DAY(シリアル値)

  • シリアル値・・・年月日を取り出したいセルを指定

 

今回は、先に完成した関数を紹介してから、その式の内容を説明します。

関数を入力した状態が下の表です。

数式バーを見ると、DATE関数が使用されているのが分かります。

=DATE(YEAR(E2),MONTH(E2)+(DAY(E2)>20),20)

DATE関数の使い方は下の通り。


DATE関数・・・年月日の情報から日付のシリアル値を返す

DATE(年,月,日)

  • 年・・・年を表す整数を入力。もしくはセルを指定。
  • 月・・・月を表す整数を入力。もしくはセルを指定。
  • 日・・・日を表す整数を入力。もしくはセルを指定。

そのまま、「年」「月」「日」を指定するので分かり易い関数です。

分かり易く、式を「年」「月」「日」に分けてみたのが下の画像です。

ピンク線の「年」の場所は、基準となる日付の場所「E2」ですね。

これで「2018」部分が取り出されます。

緑線の「日」の場所は、今回「20日」にしたいので「20」。これも問題ないですね。

ポイントは、青線の「月の」場所です。

この場所だけ、また2つに分けて考えてみます。

左側は、そのまま基準日の「月」を取り出そうとしているだけですね。

しかし、後ろの水色の場所がなぜ必要なのでしょう?

まず、もし水色線の場所がなく、「MONTH(E2)」だけだった場合を考えてみます。

関数全体は下のようになりますね。

=DATE(YEAR(E2),MONTH(E2),20)

スッキリして分かり易いのですが、この状態だと困る時があります。

下の例を見てください。

  • 参照セル(E2)の日付が「2018/5/3」の場合・・・結果「2018/5/20」・・・OK
  • 参照セル(E2)の日付が「2018/5/25」の場合・・・結果「2018/5/20」・・・もう過ぎている!

参照セルの日付が20日より前だった場合は問題無いのですが、後の日だった場合、おかしな事になってしまうのです。

これを何とかするのが、後ろの水色線の部分です。

DAY(E2)>20

この場所の意味は、

「E2」の場所が「20」より大きければ「True」で、そうでなければ「False」にしなさいという意味です。

また、エクセルでは、

  • True・・・「1」を返す
  • False・・・「0」を返す

という決まりがあります。

まとめると、

上の式の意味は、

「参照する日付が、「20」よりおおきい場合は参照する月に「1」を足し、それ以外は参照する月に「0」を足す(つまり、そのまま)」

ということになります。

 

関数の中に関数が使われて、不等号やTrueやらFalseやらで初心者の方には何が何やら意味不明かもしれませんね。

しかし、エクセルの要素が組み合わさっているともいえるので、ゆっくり理解してもらうと嬉しいです。

 

「時間が無いよー」という方は、下の画像を参考に、

矢印の場所を変更してみてください。

まとめ

日付の設定方法を2つ紹介しました。

それぞれ使い方次第で様々な設定が可能になります。

IF関数と組み合わせると細かい条件設定が可能になりそうですね。

 

 日付に関する関数はいっぱいあるにゃー
スポンサーリンク



エクセル
Prau(プラウ)Office学習所