Excel。満年齢を算出できるのが、DATEDIF関数です。【DATEDIF】

Excel。満年齢を算出できるのが、DATEDIF関数です。

＜関数辞典：DATEDIF関数＞

DATEDIF関数

読み方： デイトディフ　　

分類： 日付時刻 

DATEDIF(開始日,終了日,単位)

2つの日付の間の年・月・日数を算出する

DATEDIF関数は、関数挿入のダイアログボックスにはありません。

数式の設定は、手入力で設定します。

Excel。勤続年数や満年齢を算出するならDATEDIF関数をつかいます。【DateDifferrence】

Excel。勤続年数や満年齢を算出するならDATEDIF関数をつかいます。

＜DATEDIF関数＞

日付に関係する関数は、Excelにもたくさんありますが、便利なのに、ちょっと困った関数があります。

それが、勤続年数や満年齢を算出することができる、DATEDIF関数です。

なぜ、困った関数なのかというと、関数挿入ダイアログボックスがないからです。

つまり、手入力で、数式を作らないといけないというわけです。

ただ、引数自体は簡単なので、次の表を使って確認します。

C2に設定した数式は、

=DATEDIF(A2,B2,"y")

A2には、誕生日の日付が入力されています。

B2には、今日の日付が入力されています。

年だけで、減算してしまうと、22になってしまうので、単純に算出することはできません。

そこで、DATEDIF関数をつかうわけです。

DATEDIF関数の引数は、

DATEDIF(開始日,終了日,単位)

となっています。

今回は、開始日が、A2で、終了日がB2。

単位というのは、満年齢なら「Y」とか、月ならば「M」などが用意されています。

何ヶ月も算出したい場合には、「YM」という単位も用意されています。

満年齢を算出したいので、単位は「”Y”」となるわけです。

このように、2つの日付の間の年・月・日数を算出することができるのが、DATEDIF関数です。

ところで、読み方は、DateDifferrenceの略なので、「デイトディフ」です。

簡単な関数ではありますが、開始日を終了日が超過しないと、「1」となりませんので、注意が必要です。

Excel。DATEDIF関数で期間を計算。0年や0ヶ月を表示させないようにしたい【DATEDIF】

Excel。DATEDIF関数で期間を計算。0年や0ヶ月を表示させないようにしたい

＜TEXT+DATEDIF関数＞

在職期間や、入会期間など、開始日から今日まで何年何ヶ月という期間を算出してくれるのが、DATEDIF関数です。

ただ、このDATEDIF関数ですが、関数の挿入ダイアログボックスに表示されることもなく、数式を直接入力して作成するしか方法がない関数ですね。

ビジネスシーンではおなじみの関数ですが、何年在籍したのかという数式を作って、まずは確認します。

C4に直接数式を入力していきます。
=DATEDIF(B4,$A$1,"y")

DATEDIF関数の引数は、
DATEDIF(期間開始,期間終了,計算種別)
となっていますから、入社日が、期間開始なので、B4。

$A$1は、絶対参照を設定していますが、A1で作成した数式をオートフィルでコピーしますので、絶対参照を設定しています。なお、A1が期間終了です。

計算種別ですが、今回は年で算出したいので”Y”と設定しています。

“Y”以外にも様々な種類が用意されています。
満年はY
満月はM
満日はD
1年未満の月数は、YM
1年未満の日数は、YD
1か月未満の日数は、MD

というのが用意されています。
ではオートフィルを使って数式をコピーしてみます。

このように、在職満年数が算出できましたが、今回は、何年何ヶ月というように何ヶ月も表示させたいので、この数式を次のように修正してみましょう。

C4の数式を次のようにして、オートフィルを使って数式をコピーしました。

=DATEDIF(B4,$A$1,"y")&"年"&DATEDIF(B4,$A$1,"ym")&"ヶ月"
ちょっと長くなりましたが、たいしたことはやっていませんね。

「&」を使って数式を結合させているのがわかります。

数式の前半は、DATEDIF(B4,$A$1,"y")&"年"という数式で先程のDATEDIF関数の後ろに”年”と文字を結合させています。

後半のDATEDIF(B4,$A$1,"ym")&"ヶ月"は、何ヶ月を算出させるDATEDIF関数です。

計算種別を”YM”とすることで簡単に算出できます。
最後の"ヶ月"をつけることで、完成ですね。

【0年や0ヶ月は非表示にしたい】

しかし、この表をよくみると、0年5ヶ月や4年0ヶ月というような表示があります。

これを5ヶ月や4年というように0を非表示にしたので、再度数式を修正していきます。

D4の数式を確認してみます。
=TEXT(DATEDIF(B4,$A$1,"y"),"0年;;")&TEXT(DATEDIF(B4,$A$1,"ym"),"0ヶ月;;")

説明をしてきます。

TEXT関数を使用しています。

TEXT関数は表示形式を設定することができる関数です。
"0年;;"や"0ヶ月;;"は、「0;;」で説明すると、表示形式は、「正;負;ゼロ;文字」というルールなので、「正数は数値を表示（0）;負数は指示がないので、表示しない;ゼロも指示がないので表示しない」と設定することができます。

こうすることで、0年や0ヶ月を非表示にすることができます。

Excel関数辞典 VOL.18。CUMIPMT関数～DATEVALUE関数

Excel関数辞典　VOL.18。CUMIPMT関数～DATEVALUE関数

＜Excel関数＞

今回は、CUMIPMT関数～DATEVALUE関数までをご紹介しております。

DATEDIF関数・DATESTRING関数は直接入力でしか作れない関数ですね。

CUMIPMT関数
キュムアイピーエムティー：キュミュラティブ・イントレスト・ペイメント
元利均等返済における指定期間の金利累計を算出
CUMIPMT(利率,期間,現在価値,開始期,終了期,支払期日)


CUMPRINC関数
キュムプリンク：キュミュラティブ・プリンシプル
元利均等返済における指定期間の元金返済額累計を算出
CUMPRINC(利率,期間,現在価値,開始期,終了期,支払期日)


DATE関数
デイト
指定した日付を算出
DATE(年,月,日)


DATEDIF関数
デイトディフ
2つの日付の間の年・月・日数を算出する
DATEDIF(開始日,終了日,単位)


DATESTRING関数
デイトストリング
西暦の日付を和暦の日付に変換する
DATESTRING(シリアル値または日付文字列)


DATEVALUE関数
デイトヴァリュー
日付を表す文字列をシリアル値に変換する
DATEVALUE(日付文字列)

Excel。こういう管理はしないでほしい！元号・年・月・日が別々のセルで年齢を求めるには！

Excel。こういう管理はしないでほしい！元号・年・月・日が別々のセルで年齢を求めるには！

＜DATEDIF関数＞

日付から、年齢や職場の在籍期間などを算出するときには、DATEDIF関数を使って算出しますよね。

ただでさえ、DATEDIF関数が、手入力でないと数式をつくれない関数ということもあって、難易度が高いのに、元号・年・月・日がそれぞれ別の列で管理している表で、生年月日や入社日などを管理しちゃっていると、結構面倒というか、”お手上げ”になってしまいます。

このように、元号・年・月・日が別々のセルに入力されています。

なんで、このような表を作っちゃったんでしょうか？

日付は、基本的にというか、原理原則として、日付型で一つのセルで入力管理したほうがいいわけですね。

元号を使いたいときには、表示形式を使えばいいわけです。

と愚痴をいったところで、この表からどうやって、年齢を簡単に算出することができるのでしょうか？

DATE関数を使っても、元号を含めることができません。

F列に数式をネストでつくってもいいのですが、ここは流れを確認するために、G列をつかって、元号・年・月・日を日付（日付文字列）にしてみましょう。

G2をクリックして、次の数式を作ってみましょう。

=B2&C2&"年"&D2&"月"&E2&"日"

数式をオートフィルでコピーします。

G列に日付（日付文字列）が算出できましたね。

このように、「&」を使って文字連結をつかい、年月日も間に挟むことで、まずは、日付（日付文字列）にすることができます。

では、F列に年齢を算出していきましょう。

DATEDIF関数は、ダイアログボックスがありませんので、手入力で数式を作っていきます。

F2には、次のように入力します。

=DATEDIF(B2&C2&"年"&D2&"月"&E2&"日",TODAY(),"Y")

では、オートフィルで数式をコピーしましょう。

このように算出することできました。なかなか面倒なので、日付の管理は、重要ということがわかると、今後改善できるポイントだと思われます。

ただ、今回は、元号が昭和とか平成だったのでいいのですが…

昭和をSとか平成をHとかで表記している場合。

当然、
=DATEDIF(B2&C2&"年"&D2&"月"&E2&"日",TODAY(),"Y")
という数式では算出することができません。

このような場合には、年月日の文字をハイフン（-）に置き換えてあげることで算出することができます。

=DATEDIF(B2&C2&"-"&D2&"-"&E2,TODAY(),"Y")

では、確認してみましょう。

ハイフンに置き換えてあげることで、日付（日付文字列）にすることができますので、それを元にして、年齢や勤務年数などを算出することができました。

このように、日付の管理は、バラバラにすると、あとで何か算出等で使うときに利便性を欠くことになってしまい、効率が悪化しますので、注意するといいかもしれませんね。

もし、このような表を作る場合には、YEAR関数などをつかって、日付から年月日を表示させるようにするほうが、効率が悪化しないですみますね。

Excel。セルごとに入力されている短縮元号年月日から年齢を求めるには【DATE】

Excel。セルごとに入力されている短縮元号年月日から年齢を求めるには

＜DATEDIF&DATE関数＞

現場では様々な帳票がありまして、次のような表をまずは見てください。

 
年齢を算出している表な訳なのですが、
生年月日の年月日が、それぞれセルごとに分かれて入力されている状態でして、
一つのセルに生年月日があれば、DATEDIF関数を使えば、簡単に算出できるのに、
バラバラになってしまっているので、
算出するのが大変になってしまったケースですね。

「日付はバラさない」これが基本ですね。

しかし、このようになってしまっている場合、
どのようにしたら年齢を算出できるのかが、現場では必要になりますので、
その方法をご紹介していきましょう。

まず登場する関数はDATEDIF関数。

満年齢を算出することが出来る関数なのですが、この関数の困った点は、
手入力でないといけないということ。

関数挿入ダイアログボックスでは作ることができない、
いわゆる、『隠れ関数』な訳ですね。

そして、このバラバラになってしまっている年月日を
日付にするために必要な関数である、
DATE関数の2つの関数を使うことで算出することが出来ます。

E2の数式は、
=DATEDIF(DATE(B2,C2,D2),I1,"y")

DATEDIF関数の引数は、（開始日,終了日,算出方法）なので、
開始日である、生年月日を算出するために、
DATE関数を使って、日付を作ります。

そして、終了日が、I1。

算出方法は、満年齢なので、”y”とすれば算出してくれますね。

このケースは、西暦だったので、
開始日を作るのにDATE関数を使えばいいというアイディアは
思い浮かびやすいのですが、

次のケースはどうなのでしょうか？

【短縮元号の生年月日はDATE関数が使えない】

このような表の場合はどうでしょうか？

先程と違う点は、元号の生年月日で、
セルごとに分かれているだけではなく、昭和なら”S”。

平成なら”H”というような、
元号をアルファベットで表示した短縮元号を使っているということ。

当然、DATE関数を使っても日付になるわけはありません。

では、このケースでは算出することが出来ないのでしょうか？

意外とシンプルな方法で解決できるのですが、注意する点があります。

F5の数式を確認してみましょう。

=DATEDIF((B5&C5&-D5&-E5),I1,"y")

そう、単に、文字結合の”&”を使って年月日を作るわけですね。

ただ、注意する点は、”-“(ハイフン)を入れる必要があるという点ですね。

つまり、H10-4-5という形になれば、
Excelは日付だとわかってくれるので、
”-“(ハイフン)をいれる必要があるわけですね。

このように、年月日をセルごとにわけて入力してある表の場合は、
工夫が必要になってしまいますので、出来ることならば、
分けないでほしいものですね。

Excel。TEXT&DATEDIF。開始日から終了日までの期間を年ヶ月で表示するにはどうしたらいい？

Excel。開始日から終了日までの期間を年ヶ月で表示するにはどうしたらいい？

＜TEXT関数とDATEDIF関数＞

開始日から終了日の間の日数を、「年月日」で表示するのではなくて、
その間の期間を「年ヶ月」という形で表示したいのですが、
うまくいかないので困っているというリクエストがありました。

そこで、今回は、期間を年ヶ月で表示する方法をご紹介していきましょう。

下記のような表があります。

B列に入会日があって、今日までの入会期間を求めるというのがC列。という表です。

このような期間を求める時に登場するのが、DATEDIF関数ですね。

このDATEDIF関数は、関数挿入のダイアログボックスから見つけることが出来ない関数なので、
直接入力していく必要があります。

では、C2に次の数式を設定してみましょう。

=DATEDIF(B2,TODAY(),"y")

そして数式を入力して、オートフィルで数式をコピーしておきましょう。

=DATEDIF(B2,TODAY(),"y")

は、その間の年数を算出してくれます。
よって年数を求めるだけでしたら、この数式でいいのですが、求めたいのは「年ヶ月」。

そこで、&を使って、文字と数式を結合して、次のように数式を修正していきましょう。

=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"ヶ月"

そして、オートフィルで改めて、数式をコピーしてみましょう。

どうでしょうか？
今日までの期間を「年ヶ月」で表示することが出来ましたね。
ちなみに、数式に追加した、

DATEDIF(B2,TODAY(),"ym")

これは、年を除いた月数を求めることが出来る数式ですね。

しかし、確かに、「年ヶ月」で表示は出来たのですが、C2とC７。

“0ヶ月”と表示されていますが、これ、なんかカッコ悪いですよね。
“0ヶ月”だったら、表示しないで、何年。と表示できた方がいいですよね。

IF関数を使って判断させるようにしてもいいのでしょうけれど、0ヶ月かどうかを判断させて、
それによって、算出方法を変えられるようにするとしたら、数式が煩雑になってしまいます。

そこで、TEXT関数を使ってみると、今回の0ヶ月を非表示にするように変更していきましょう。

このTEXT関数は、表示形式を変更設定してくれる関数ですね。
でどのように使うのか？
というと、0（ゼロ）だったら、表示しないようにしたいわけです。

このBLOGでも度々登場しているテクニックですが、表示を消したいときには、
表示形式のユーザー定義を;;;（ゼミコロン×3）にすると良かったわけですよね。

となると、ユーザー定義で、”0年;;”と”0月;;”としてあげればいいわけですね。

表示形式は、正数 ; 負数 ; 零 ; 文字　の順番で表示方法を設定しておりますので、
”0年;;”とすれば、いいわけです。

では、C2の数式を次のように修正してみましょう。

=TEXT(DATEDIF(B2,TODAY(),"y"),"0年;;")&TEXT(DATEDIF(B2,TODAY(),"ym"),"0ヶ月;;")

あとは、オートフィルで数式をコピーしましょう。

これで、完成しましたね。

このように、単に、「年ヶ月」を表示させる方法をプラスアルファして、
0ヶ月などの0を非表示する方法も加えてみました。

何か機会がありましたら、
TEXT関数とDATEDIF関数の組み合わせを使ってみてはいかがでしょうか？

Excel。DATEDIF関数を使って、勤続年数を求めると何かがおかしい？

Excel。DATEDIF関数を使って、勤続年数を求めると
何かがおかしい？

DATEDIF関数

Excelで勤続年数。すなわち、満年齢を求める時に使うことが多い、DATEDIF関数。

このDATEDIF関数は、関数の挿入ボタンからダイアログボックスを表示することができない、
いわば、【隠し関数】なのですが、このDATEDIF関数を使った場合、
会社さんのルールによっては、うまく算出されてないことがありまして、
12月や年度末が近付いてきますと、よくご質問を受けることがありますので、
今回は、DATEDIF関数のご紹介とご質問に関しても書いてみようと思います。

下記の表があります。

まずは、DATEDIF関数の作り方と、その算出の結果を確認してみましょう。
D3に満年齢を算出させてみましょう。見た目で、すでに3歳とわかりますが、
DATEDIF関数をD3につくっていきます。

通常ならfxの関数挿入ボタンをクリックして、ダイアログボックスを表示したりしますが、
このDATEDIF関数は【隠し関数】なので、見つけることができません。

なので、数式バーに直接入力するしかありません。

=DATEDIF(B3,C3,"y")

と入力しましょう。”y”は、大文字でもOKです。
また、引数。（）の中は、開始日,終了日,算出する期間（単位）です。
ちなみに、単位には下記の種類があります。

"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の満日数

さて、結果は、3ですね。

自分の誕生日を迎えましたので、3歳で間違いありませんね。
では念のため、C3を2015/1/4にしてみましょう。

当然のことながら、2になりましたね。誕生日前日ですから、2歳で問題ありませんよね。

このように、DATEDIF関数は満年齢などを算出してくれるわけです。
さて、続いでは勤続年数。
算出しなくても、見た目で、4/1～3/31なので、1年働いたということで、
1年契約だったりしますと、このようなケースがあると思われますが、
これ、DATEDIF関数を使って勤続年数を算出してみましょう。

D6をクリックして、DATEDIF関数を作成しましょう。

=DATEDIF(B6,C6,"y")

という数式ですね。”y”は大文字でもOKです。結果はというと…

0と算出されてしまいました。数式も結果も間違ってはいません。

ただ、これでは、勤続年数を算出した結果がイメージと異なってしまいます。
2015/4/1まで勤務しないと1年契約にならないのでしょうか？
これは、会社さんによってルールがあるかと思いますが、

イメージとしては、4/1～3/31で勤続年数には1という結果になってほしい訳です。

このDATEDIFは満年齢をを算出する関数なので、最初にやりましたように、
年齢のように誕生日を迎えると年齢がプラス1されるわけです。

つまり、今回の4/1～3/31のような場合では、一日足りませんので、
＋1してあげる必要がありますので、イメージ通りの結果を出したい場合には、
D6の数式を

=DATEDIF(B6,C6+1,"y")

と終了日に＋１してあげる必要がありますで、調整が必要となります。

なお、実践実務ということもあって、マンツーマン講習を中心にご紹介しております。

Excel。誕生日から満年齢を求めるのにDATEDIF関数よりもYEARFRAC関数はいかが？

Excel。誕生日から満年齢を求めるのにDATEDIF関数よりもYEARFRAC関数はいかが？

YEARFRAC関数

前回ご紹介の続きとなりますが、満年齢を求めるのにはどうしたらいい？ということで、
DATEDIF関数をご紹介しましたが、このDATEDIF関数。関数の挿入ダイアログボックスでは、
見つけることができませんので、手入力で数式を作成するというお話をさえていただきました。

仕事でつかえるExcel講座や、企業研修などでも、このDATEDIF関数は紹介するのですが、
やはり、手入力ということだけあって、ちょっと取っ付きにくい。

そこで、満年齢だけでしたら、別の方法。
つまりYEARFRAC関数を今回ご紹介したいと思います。

このYEARFRAC関数もなじみがないと思いますが、このYEARFRAC関数は、
関数の挿入ダイアログボックスから検索して、
YEARFRAC関数のダイアログボックスも表示されますので、数式を作成する場合は、
YEARFRAC関数のほうがわかりやすいという面もあります。

まず、今回は、E5に、YEARFRAC関数を使って、満年齢を求めていきます。
E5をクリックして、YEARFRAC関数のダイアログボックスを表示しましょう。

開始日には、誕生日のC5を入力します。
終了日には、日付のD5を入力します。

そして、基準ですが、この意味はというと「計算に使う基準日数を示す数値」を入力します。
基準 基準日数 (月/年)
0 または省略 30 日/360 日 (NASD 方式)
1 実際の日数/実際の日数
2 実際の日数/360 日
3 実際の日数/365 日
4 30 日/360 日 (ヨーロッパ方式)

という設定ができるようになっておりますので、ここは実際の満年齢ですから、
基準には、1と入力します。

そして、OKボタンをクリックしましょう。

E5は、小数点になっているので、三桁区切りにできる、桁区切りスタイルを設定しましょう。
”，”のボタンですね。そうすると、

34と表示されましたね。
DATEDIF関数よりも、こっちのほうが分かりやすいと思いますが、ただ、基準が厄介ですよね…。
といことで、せっかくなので0～4まで結果がどうなるのかやってみましょう。
E5をクリックして、表示形式を標準に戻しておきましょう。
また、わかりやすいようにD5の日付を2013/12/31にしておきましょう。

では、基準0または、省略では、どのような数値になるのかやってみましょう。

結果は、34になってしまいましたね。これは使えませんね。そりゃそうですね。
360日で計算している訳ですから。

1は、やりましたので、続いて2をだとどうなるでしょうか？

結果は、これもダメ…

使えませんね。そりゃ、360で割ったらだめですよね。
続いて３ですが、365で割ってもいいけど。

結果は、うるう年を無視。これもダメ。

では、最後に4をやってみようと思いますが、

結果は、33になるのでいいのかなぁ～と思いますが。

実際の日数ではないうえに、360で割っていますので、偶然という結果。
なので、ここは、当然と言えば当然なのですが、基準は、1を使うわけですね。

ということで、YEARFRAC関数をご紹介しました。

Excel。誕生日から満年齢を求めるのには、DATEDIF関数を使ってみる。

Excel。誕生日から満年齢を求めるのには、
DATEDIF関数を使ってみる。

DATEDIF関数

Excelで結構面倒なのが、日付関係の計算。
これは、Excelが1990/1/1を1として一日をプラス1ずつしていくシリアル値を使い、
表示形式で日付をコントロールしているからなのです。

そして、結構あちこちの現場とか、仕事でつかえるExcel講座などで、
教えてほしいとリクエストがあるのが、満年齢とかの、
満年を求める方法というのがありますので、
今回は、以前に紹介したことがあるDATEDIF関数を改めて、ご紹介します。

下記の表があります。
E3に満年齢を求めるとしたケースは、単純に、D3-C3という計算をしたとします。

その結果は、12543

誕生日から今日の日を引いたとしても、シリアル値になってしまい、
この期間の日数は算出することが出来ますが、年数はわかりません。
表示形式を日付にするとますます、訳が分からなくなってしましますし…

この12543を単純に365で割ればいいという話ではありませんね。うるう年の問題があります。

そこで、年齢や期間を求めるには、DATEDIF関数を使う方法が有名なので、
DATEDIF関数を紹介します。

このDATEDIF関数は、2つの日付間の日数、月数、年数を返しますが、
ちょっと困った関数なのです。DATEDIF関数は、
Lotus 1-2-3の関数との互換性を保つために導入された経緯があるために、
通常通りに関数の挿入ボタンをクリックして、
関数挿入ダイアログボックスからDATEDIF関数を見つけることができないのです。

DATEDIF関数は、【隠れている】のです。

では、
どうやって関数を作っていくのかというと…【手入力】でミスなく入力するという方法になります。

Microsoftさんも、そろそろ標準搭載にしてくれてもいいと思うのですがね…

それでは、E4に入力する数式は、
=DATEDIF(C4,D4,"Y")
となります。
引数。（）の中は、開始日,終了日,算出する期間（単位）です。
単位には下記の種類があります。
"Y" 期間内の満年数
"M" 期間内の満月数
"D" 期間内の満日数
"MD" 開始日から終了日までの日数。この場合、月と年は考慮されません。
"YM" 開始日から終了日までの月数。この場合、日と年は考慮されません。
"YD" 開始日から終了日までの日数。この場合、年は考慮されません。

算出されて結果は34。

ということで、満３４歳ということがわかりました。
今回は、今回は説明のために、D列に日付として、2014/5/5と入力しておりますが、
大体満年を算出する場合は、
誕生日とか入社日とか入会日から今日までというケースが主だと思われます。

その場合は、DATEDIF関数の終了日のところに、TODAY関数を使ってあげればいいですよね。

その場合の計算式は、
=DATEDIF(C4,TODAY(),"Y")
というDATEDIF関数で求めることができます。

余談ですが、E4をクリックした状態で、
fxボタンを押して、DATEDIF関数のダイアログボックスを表示してみましょう。

単に、ダイアログボックスが表示されただけで、ラベルもないので、
入力するボックスの意味もわかりませんね。

このDATEDIF関数。確かに便利ではありますが、隠れているうえに、
ダイアログボックスが使えないので、手入力で作るため、なかなか覚えているのが大変です。

そこで、次回は、DATEDIF関数ではなくて、ある関数を使うと、満年齢だけですが、
分かりやすく数式を作ることが出来るので、その関数。

YEARFRAC関数をご紹介したいと思います。