今回の記事では
関数を使って日付を計算するにはどうしたら良いの？
日付の期間を簡単に計算出来たら手間が無くなるのに
イベントなどの期限を決める時に自動的に日付を計算してくれる方法って無いの？
関数を使った方法やその他の計算方法があるなら知っておきたい
こういった疑問に答えます
✔日付を上手に計算する方法のテーマ
①日付の期間を計算したいならDATEDIF関数を使うのが最適解です
②期限を自動で計算したい場合はDATE関数を使うのがベストです
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「日付を上手に計算する方法のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
日付の期間を計算したいならDATEDIF関数を使うのが最適解です
日付の計算で期間を計算したいならDATEDIF(デイトディフ)関数を使うのが一番です
なぜなら、日付の期間を計算する最適な関数がDATEDIF関数だからです
今回は、釣り道具の使用期間の計算方法を基にDATEDIF関数の使い方を解説していきます
但し、DATEDIF関数は一般的な関数と違って直接入力する以外の方法では使え無いので注意して下さい
また、今回用意した各資料はあくまで例題のため、実際の使用年数やイベント開催日は全てダミーとなります
こんな感じで現時点での使用期間を表示することが出来ます
いきなりこの表示にするのはハードルが高いので
まずは、DATEDIF関数の基本的な使い方で「年・月・日」を表示させる方法から解説していきます
まず、現在の日付から購入日までの年数を表示する方法は
設定内容はこんな感じです
式は「=DATEDIF($C7,$B$4,“Y”)」ですね
基本的な使い方は「=DATEDIF(開始日,終了日,表示方法)」です
DATEDIF関数の使い方は「日付の差の計算はDATEDIF関数を使えばOK」という記事でも紹介しているので参考にしてみて下さい
DATEDIF関数の一番の特徴は表示方法にあります
年月日を「Y=YEARの頭文字」「M=MONTHの頭文字」「D=DAYの頭文字」として「”(ダブルクォーテーション)」で囲むことで表示内容を変えてくれます
つまり今回の式を日本語にすると「=DATEDIF(C7セル～,B4セルの,「年数」を表示してね)」という意味になります
分かってしまえば簡単ですよね
その他の表示方法は下記の通りです
| 表示方法 | 意味 |
|---|---|
| Y(y) | 期間内の満年数 |
| M(m) | 期間内の満月数 |
| D(d) | 期間内の満日数 |
| YM(ym) | 年数を無視した月数 |
| YD(yd) | 年数を無視した日数 |
| MD(md) | 年数と月数を無視した日数 |
次は月数を見てみましょう
基本は年数と同じなので難しくは無いですよ
設定内容がコレです
式は年数の設定と同じですね
違いは「表示方法」だけです
「=DATEDIF($C7,$B$4,”M”)」は対象期間(購入日～今日まで)の「総月数」を表示してくれています
実務ではそこまで使う機会が無いですね
続いて日数の表示方法です
基本的な使い方は全く同じなので問題ないと思います
月数と同じように期間の総日数を表示してくれています
式の内容は「=DATEDIF($C7,$B$4,”D”)」なので簡単ですね
「××まであと〇〇日」といった使い方には向いていますが、こちらも一般的にはあまり使わない設定ですね
こんな感じで「Y・D・M」はこんな風に使うんだと分かってもらえればOKです
次は「YM・YD・MD」の使い方です
まずは「YM」です
こんな感じで1年未満の月数を表示してくれます
つまり、年数を無視して1月～翌年の6月を「１月・2月……といった具合に月数だけ数える」という機能になります
これ単体ではなかなか使わない機能ですが後々重要になってきます
使い方は同じなので解説は省略します
次は「YD」ですね
これはさっきの年を無視した月数の数え方と同じで「年を無視した日数を数えてくれる機能」です
これもあまり使わないですね。使い方は今までと全く一緒です
では「MD」の使い方にいってみましょう
これは、「年数と月数を無視した日数」を数えてくれます
これも後で役立つ方法なので覚えて損は無いです
最後に使用期間を年月日で表示する方法を紹介します
その方法がコレです
式を見ると複雑そうですが、バラしてみれば今までの結果を繋げただけだと分かります
早速式を見てみましょう
計算式はこんな感じですね
「=DATEDIF($C7,$B$4,”Y”)&”年”&DATEDIF($C7,$B$4,”YM”)&”ヶ月”&DATEDIF($C7,$B$4,”MD”)&”日”」
めっちゃ長いですがコレを3分割してみます
①「DATEDIF($C7,$B$4,”Y”)&”年”&」
②「DATEDIF($C7,$B$4,”YM”)&”ヶ月”&」
③「DATEDIF($C7,$B$4,”MD”)&”日”」
これでキレイに別れました
①は年数を表示して「年」という文字とくっつけています
②は「YM」なので「年を無視した月数=ヶ月」を表示して「ヶ月」という文字とくっつけています
③は「MD」なので「月を無視した日数」を表示して「日」という文字とくっつけています
この組み合わせにすると使用期間をこんな風にスムースに表示出来ます
例えば、「誕生日や勤続年数」を表示したい時に使われることが多いやり方です
また、それぞれの数字の後ろに対応した文字をくっつけることで、見やすく表現することも出来ます
こんな風に文字列と結合することで使い方の幅が広がるので、ぜひチャレンジしてみて下さい
繰り返しになりますが、日付の計算で期間を計算したいならDATEDIF(デイトディフ)関数を使うのが一番です
期限を自動で計算したい場合はDATE関数を使うのがベストです
期限日を自動で計算したい場合はDATE関数などを活用するのがベストです
なぜなら、DATE関数は数字を日付(シリアル値)に変換してくれる便利な関数だからです
それでは具体的な設定方法について見ていきましょう
今回は「桜まつりの開催予定日」と「春祭りの開催予定日」を例に「日数・月数・年数」を求める方法について解説します
今回の目的は「桜の見頃の日付から3日後を桜まつりの開催予定日」として自動で表示
「桜の見頃から1ヶ月後に春祭りの開催予定日」を自動で表示
「桜まつりから1年後に来年度の桜まつりの予定日」を自動で表示することです
こうしておくことで「桜の見頃の日付を入力」すれば全ての日付が自動更新されるようになります
その都度予定日を設定し直さなくて済むので便利ですよね
では、この表の作り方を順番に見ていきましょう
まずは、「桜まつりの開催予定」である「見頃の日付から3日後」を自動で表示する方法です
※日数・月数・年数は表示形式を設定しているだけなので正体はただの数字です
今回の式は「=DATE(YEAR($B9),MONTH($B9),DAY($B9)+$B$4)」です
複雑に見えますが「DATE関数」の中に「YEAR・MONTH・DAY」関数で日付を設定して「B4セルの日数を足している」だけです
つまり「2022/3/11+3日=2022/3/14」としてる訳です
理屈は簡単ですよね
まず、DATE関数の使い方は「DATE(年,月,日)」です
説明しなくても分かるくらい簡単ですね
例えば「=DATE(2022,3,11)=2022/3/11」と同じ意味になる訳です
次に「YEAR・MONTH・DAY関数」は「YEAR(2022/3/11)→2022」といった具合に日付から年数・月数・日数を抜き出してくれる関数達です
コレを組み合わせたのが「DATE(YEAR($B9),MONTH($B9),DAY($B9)」の部分です
そして「何日後」を表示させたいので日付である「DAY($B9)の結果にB4セルの3を足してあげる」ことで3日後の日付を表示させているという訳です
文字にすると複雑そうですが、意外と簡単に知りたい日付を表示することが出来ます
今回のポイントは、予想見頃の日付から各関数を使って年月日を分けてもう一度DATE関数で日付に戻している点です
次は春祭りに開催予定日を自動で表示出来るようにしてみましょう
次は「春祭りの開催予定日」を自動的に計算する方法です
「春祭りは見頃から1ヶ月後」なので設定はさっきとほぼ一緒ですね
違いは月数に日数を足している点です
設定内容は「=DATE(YEAR($B9),MONTH($B9)+$B$5,DAY($B9))」なので月数に「+D5のセルが設定」されていますね
こんな風に簡単に1ヶ月後の日付も設定してくれます
最後に来年のイベントの日付を表示してみましょう
内容はこんな感じです
1年後の設定がコレです
今までとほぼ変わらないですね
違いは年数の参照先が「C9のセル」になっているだけです
そして、年数である「B6のセル」を足しているだけなので基本的には一緒です
こんな風に一つのやり方を知ってしまえば他でも役立つのでぜひチャレンジしてみて下さい
繰り返しになりますが、期限日を自動で計算したい場合はDATE関数などを活用するのがベストです
よくある質問：日付の計算は分かったけど月末の日数がおかしい時があるんだけど、これってどうにかならないの？
月末の日付がおかしい時はEDATE関数かEOMONTH関数を使うのが正解です
なぜなら、両方とも日付を扱う関数なので月末の日付に左右されることが無いからです
では、具体的な方法について見ていきましょう
EDATE関数で来年の月末を正しく表示する
例えば、こんな風に1年後の2月の月末を表示したいのに3月が表示されてしまうことがあります
この原因は「うるう年」が関わってくるからです
日付にはうるう年をはじめとして1日以上の誤差があります
この誤差があるので1日以上のズレが発生してしまいます
次の月が表示されるのは、間違いでは無いですが正しい来年の月末を表示したい時は「EDATE関数」を使うと解決出来ます
こんな風にちゃんと「2月28日」の表示になりました
EDATE関数の基本的な使い方は「=EDATE(開始日,月)」です
なので「=EDATE(B4,12)」を日本語にすると「=EDATE(B4のセルの,12ヶ月後の日付を表示して)」という意味になります
12ヶ月後なので一年後である「2021/2/29」となりますが「29日は無い」ので自動的に正しい日付である「2021/2/28」が表示されたという訳です
EOMONTH関数で正しい月末を表示する
月末の正しい日付を表示してくれる関数と言えば「EOMONTH関数」です
実際の設定方法を見てみましょう
設定方法はこれだけです
EOMONTH関数の基本的な使い方は「=EOMONTH(開始日,月)」なのでEDATE関数と一緒ですね
今回は「=EOMONTH(B4,0)」でコレを日本語にすると「=EOMONTH(B4のセルの日付の,当月の月末の日付を表示してね)」という意味です
「0」は指定した月という意味があるので月末の日付を表示したい時に活用しましょう
どちらの関数を使っても結果は同じなので好きな方を使って下さい
月末は4種類あるのでこの2つの関数を上手く活用して厄介な月末表示を自動化しましょう
繰り返しになりますが、月末の日付がおかしい時はEDATE関数かEOMONTH関数を使うのが正解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
![VLOOKUP関数で０やエラー表示を避ける方法［IF・IFERROR関数との組み合わせ]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/4327107_m.jpg?fit=1200%2C900&ssl=1&resize=40%2C40){kind=small}
コメントを残す