Excel。日付を直接入力した数式はシリアル値で考える必要があります。【DATE】

Excel。日付を直接入力した数式はシリアル値で考える必要があります。

＜IF+DATEVALUE関数＞

判定に使う日付をセルに入力している場合には、いいのですが、直接日付を引数に入力した場合、きちんと算出してくれないことがあります。

次の表を用意しました。

B列に作業終了日という日付が入力されています。

C列には、2024/10/31以前ならば、○。

それ以降ならば、×と算出したいと考えています。

そこで、IF関数をつかって、C2に数式をつくります。

=IF(B2<=2024/10/31,"○","×")

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

すべてC列は、2024/10/31以前も以降も関係なく、×になっています。

C2の数式を確認するために、数式タブの「数式の検証」を行ってみると原因がわかります。

2024/10/31の「2024/10」を数式と勘違いして、2024÷10として、さらに÷31をしています。

つまり、B列の日付がシリアル値という数値で、C列は「/」を割り算と勘違いして、除算した結果の数値と比べているために、結果がおかしくなっています。

日付として扱ってくれていないわけです。

だったら、”2024/10/31”と「”（ダブルコーテーション）」で日付を囲ってみたらどうなるのでしょうか。

今度は、すべて「○」となってしまいました。

これは、”2024/10/31”が日付ではなくて、文字列として判断されています。

そのため、シリアル値という数値と文字を比べていますので、当然、シリアル値である日付のほうが小さくなります。

よって、結果は「○」となってしまったというわけです。

紹介した両方とも、「日付」として扱ってくれていません。

Excelには日付はシリアル値という数値です。

日付型ではありません。

そこで、日付型というか、日付とExcelにわからせる必要があります。

そこで、DATEVALUE関数をつかって、数式を修正します。

=IF(B2<=DATEVALUE("2024/10/31"),"○","×")

DATEVALUE関数は、日付文字列にしてくれる関数です。文字列を日付扱いにしてくれます。

これで、きちんと算出してくれました。

このように、日付を直接入力して使う場合には、日付文字列にする必要がある場合、DATEVALUE関数を組み合わせることで対応することができます。

Excel。DATEVALUE関数で、日付を表す文字列をシリアル値に変換できます。【DATEVALUE】

Excel。DATEVALUE関数で、日付を表す文字列をシリアル値に変換できます。

＜関数辞典：DATEVALUE関数＞

DATEVALUE関数

読み方： デイトバリュー　

分類： 日付時刻 

DATEVALUE(日付文字列)

日付を表す文字列をシリアル値に変換する

Excel。数式に直接日付をつかって処理する時にシリアル値で考える必要があります。【serial number】

Excel。数式に直接日付をつかって処理する時にシリアル値で考える必要があります。

＜IF+DATEVALUE関数＞

条件とする日付を入力したセルを用意するのではなく、数式に直接日付を設定する場合には、ちょっとしたアイディアが必要になります。

次の表で説明します。

C列には、B列のデータが2022/11/30までに提出されていれば、「○」を表示するようにしたいわけです。

別のセルに2022/11/30という条件を入力していないので、直接数式の中に、日付を入力した数式をつくることになるわけです。

C2の数式は、

=IF(B2<DATEVALUE("2022/12/1"),"○","×")

と設定してあります。

なぜ、日付の前に、DATEVALUE関数をつかっているのか説明をしていきます。

C2に、次の数式を用意して、オートフィルで数式をコピーしてみます。

=IF(B2<"2022/12/1","○","×")

C5の判定がおかしいことがわかります。

条件の日付に「”（ダブルコーテーション）」をつかって、「"2022/12/1"」としたのが、いけないのかと考え「2022/12/1」とダブルコーテーションを消してみます。

=IF(B2<2022/12/1,"○","×")

結果は、ますます、おかしくなってしまいます。

 なぜ、このようなことになってしまうのか。

その原因は、「日付」はシリアル値だということ。

つまり、「数値」なんだということです。

最初の「”（ダブルコーテーション）」をつかってしまうと、「文字」になってしまうので、「2022/12/1」という文字よりも小さいという条件になってしまったわけです。

二つ目の「”（ダブルコーテーション）」が無い数式は、「数値は数値」なのですが「2022/12/1」という除算した結果より小さいかという条件になってしまったわけです。

数式タブの「数式の検証」をつかってみると、よくわかります。

 このようにシリアル値の問題がある場合には、日付を数値に変えて判断させることが必要になります。

そこで、DATEVALUE関数をつかうというわけです。

C2の数式をDATEVALUE関数のネストに変更します。

=IF(B2<DATEVALUE("2022/12/1"),"○","×")

オートフィルで数式をコピーすれば、完成するというわけです。

数式タブの「数式の検証」でこの数式を確認してみると、動きが理解できます。

このように、数式内に直接日付を使用した時に、結果がおかしい場合には、シリアル値のことを考えてみると、対応できるかもしれませんね。

Excel。データを読み込んだら日付文字列だったので、日付型にしないと不便です【DATEVALUE】

Excel。データを読み込んだら日付文字列だったので、日付型にしないと不便です

＜DATEVALUE関数＞

テキストデータをはじめ、外部からExcelにデータをインポートした時など、日付が日付文字列になってしまっていることがあります。

日付文字列になってしまうと、文字列なので、その日付から10日後などという、数式等々でつかえません。

そこで、「DATEVALUE関数」をつかえば簡単に、日付文字列を日付型に変更してくれます。

A列には、日付文字列で入力された日付が入力されています。

A2をアクティブにした状態で確認してみると、数式バーは、2023/1/25と表示されていません。

つまり日付文字列になっていることがわかります。

ではC2に次の数式を設定します。

=DATEVALUE(A2)

オートフィルで数式をコピーして、C4まで設定すると、「シリアル値」に変換された状態で算出されます。

このDATEVALUE関数は、日付を表す文字列をシリアル値に変換する関数なので、表示形式までは設定してくれません。

算出後、表示形式を変更してあげれば、D列のように、日付にすることができます。

もし、日付型でなく日付文字列になってしまったデータがあった時には、DATEVALUE関数をつかってみるといいかもしれませんね。

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。和暦を西暦に変換。けど日付がドットで区切られていて…どうしたらいい？

＜DATEVALUE関数＞

なんでも前任者さんが使っていた、古いExcelファイルだそうでして、その中に生年月日の欄があるのですが、なんと次の表のようになっていて、困っているとのこと。

どうやら、以前用紙で入力されていたものを、そのままExcelに入力したのではないかとのこと。C列には元号の頭文字が、そして、なんと生年月日が35.7.21と年月日が入力されていて、しかも、年と月の間には、ピリオドというかドットが入力されていて、区切られているというD列があるわけですね。

確かに、これでは、西暦にするにしても、年齢を算出するにしても、どうしようもありませんよね。

ということで、今回はこのような状態の場合にどうしたらいいのか？をご紹介していきます。

E列に、元号を生年月日に結合させればいいのでは？と考えると思いますが、どうなるのかを確認してみましょう。
E3をクリックして、=C3&D3
S35.7.21
と表示されましたが、ただ、文字を結合しただけで、表示形式を変えようとしても、変えることが出来ません。
では、一から入力しなおさないといけないのでしょうか？

そこで、登場するのが、【DATEVALUE関数】です。

それでは、E3をクリックして、DATEVALUE関数ダイアログボックスを表示しましょう。
難しくない関数なので、直接手入力でもOKですよ。

日付文字列には、C3&D3　と入力しましょう。
OKボタンをクリックしましょう。
数式は、
=DATEVALUE(C3&D3)
E3には、このように算出されます。

E3に表示されたのは、シリアル値。なので、表示形式を使って西暦の年月日の表示に変更していきます。その後、オートフィルで数式をコピーします。

このように、【DATEVALUE関数】を使ってあげれば、日付に変換することが出来ます。

この【DATEVALUE関数】は、結構実務的に役立つ関数で、次のようなケースでも使える関数です。

このケースは、年月日がそれぞれ、別の列に設定されている場合です。
この場合には、次のように【DATEVALUE関数】を使ってあげれば、日付にすることが出来ます。

DATEVALUE関数ダイアログボックスを表示して、今回は、日付文字列には、
C3&D3&"-"&E3&"-"&F3
と入力します。
数式は、
=DATEVALUE(C3&D3&"-"&E3&"-"&F3)
あとはOKボタンをクリックして、表示形式を変更するのと、オートフィルで数式をコピーすれば、完成になります。

元号がアルファベットの頭文字。すなわち、明治のM。大正のT。昭和のS。平成のH。というケースでは、-（ハイフン）で結合してあげる必要があります。

また、今回のC列が、明治・大正・昭和・平成という文字になっている場合の数式は、
=DATEVALUE(C3&D3&"年"&E3&"月"&F3&"日")というように、
年月日という文字で結合させる必要がありますので、ご注意ください。

まぁ、出来ることなら、Excelの入力は、2017/10/18のように入力してあげるのが、いいのかもしれませんね。