8桁の数値や数字を日付に変更する:Excel関数の技

スポンサードリンク

更新:2024/7/9;作成:2007/9/17

8桁の数値を日付に変更する	日付のシリアル値
日付の比較	文字列や数字を日付(シリアル値)に変換する

8桁の数値を日付に変更する　　 topへ

8桁の数値で表現された日付　「20240701」　を日付データに変換するには、TEXT関数を使うと便利です。
下図の例では　=TEXT(A2,"0000!/00!/00")*1としています。
- =TEXT(A2,"0000/00/00")*1　とスラッシュのみではうまくできませんので、感嘆符（ビックリマーク）をつけて=TEXT(A2,"0000!/00!/00")*1　とします。
  「!」につづく1文字はそのまま表示されるので、日付の表示形式として利用できています。
  最後の*1は日付の文字列を演算することでシリアル値に変換しています。
=TEXT(A2,"0000-00-00")*1　とすることもできます。
こちらの方がハイフンだけの入力ですむのでスマートに見えませんか？
左の4文字が年、真ん中の2つが月、右の2文字が日となっているので、
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))　とすることもできます。

A列に8桁の数値が入力されています。
C2セルに　=TEXT(A2,"0000!/00!/00")*1　と入力して、下方向へ数式をコピーします。
セルの表示形式が「標準」だと、日付のシリアル値が表示されます。
C2:C11セルを選択して、[Ctrl]+[1]でセルの書式設定ダイアログボックスを表示します。
表示形式で　「*2012/3/14」　を選択しました。
8桁の数値を日付に変換できました。

文字列のデータを選択します。
文字列のデータにするため各セルのデータは '2007/14/1のように、頭に '(アポストロフィ)を入力しています。
データタブのデータツールグループの[区切り位置]を実行します。
区切り位置ウィザードの1/3,2/3は[次へ]ボタンをクリックします。
区切り位置ウィザードの3/3で「日付」にチェックを入れます。
表示先をB2にして、[完了]をクリックします。
説明の上で「B2」としていますが、「A2」とするとデータを置き換えることができます。
C列のように変換されます。値はシリアル値になっています。
文字列は左揃えになっていますが、シリアル値は右揃えになっています。
Excel for Microsoft365では表示形式も勝手に適用されました。

DATEVALUE関数で変換することもできます。
しかし、一部の文字列は変換できませんでしたので、ほかの関数を使いました。
A6,A7セルは =TEXT(A4,"0000!/00!/00")*1　と文字列でも数字でも8桁の数値を日付に変更できます。
A8セルのように、ドットで区切られている　「20024.7.10」　というケースでは　ドットをスラッシュに変えることでExcelが日付と認識できます。
よって、　=SUBSTITUTE(A8,".","/") として、変換しますが、これは文字列なので、DATEVALUE関数で日付データにします。
　=DATEVALUE(SUBSTITUTE(A8,".","/"))　とします。
または、=SUBSTITUTE(A8,".","/")*1　でもOKです。
シリアル値に変換後は表示形式を短い日付形式にしています。

Excelでは日付をシリアル値という連続した値で処理します。
Windows版Excelは1900年日付システムですが、Macintosh版Excelでは1904年日付システムが使用されています。
- 1900年日付システムは1900年1月1日を1として1日に1ずつ増えます。
  - ただし、1900年2月29日は実在しませんが、この1900年日付システムには存在していますので、1900年3月1日よりも前の日付を扱う時には注意が必要です。
- 1904年日付システムは1904年1月1日を1として1日に1ずつ増えます。
  - [ツール]→[オプション]の[計算方法]タブで「1904年から計算する」のチェックボックスをオンまたはオフにして切り替えることが出来ます。
  - ただし、作成途中で切り替えると日付にズレを生じますので注意しましょう。
セルに入力されている「日付」がシリアル値か否かはセルの表示形式を「標準」にします。
すると、「2024年1月1日」は「45292」という数値(シリアル値)になります。

Excelはセルに入力されたデータ値を日付であると検知した場合、自動でシリアル値に変換します。
さらに、日付の表示形式を自動で適用してくれたりします。
- 1/1、1-1と入力すると2024年1月1日と判断し、シリアル値「45292」に変換します。表示は1月1日となります。
  そして数式バーには2024/1/1と表示されます。
  (この時の年はパソコンのカレンダー(日付)の年を取得します。)

日付同士を比較する時、セル参照では問題ないが値を数式に書くとうまくできないことがあります。
=A1>"2024/6/30"　の場合、Excelが　"2024/6/30"　を文字列と認識するためです。
C5セルのように、"2024/6/30"*1　とか　"2024/6/30"+0　と演算をしてシリアル値に変換して比較するとOKです。
C7セルのように、=A2>VALUE(2024/6/30)　とすることもできますが、*1の方が入力が早くできます。
文字列"2024/6/30"を"2024/6/30"*1のような計算式にすることで、Excelが日付と認識してシリアル値に変換して計算します。
DATE関数を使ってシリアル値にして比較することもできます。
=A1>DATE(2024,6,30) として、日付データにして比較します。
引数をセルを参照する形式にするなら、　=A2>DATE(YEAR(B2),MONTH(B2),DAY(B2))　とやりそうですが、=A2>B2　でOKです。

スポンサードリンク

Home » エクセル関数の技 » 8桁の数値や数字を日付に変更する

　PageViewCounter
　 Counter
　Since2006/2/27