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

スポンサードリンク

更新:2025/4/3;作成: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　と入力して、下方向へ数式をコピーします。
セルの表示形式が「標準」だと、日付のシリアル値が表示されます。
Excel for Microsoft365,Excel2021以降ならSpillが使えるので、=TEXT(A2:A7,"0000!/00!/00")*1　とします。
C2:C11セルを選択して、[Ctrl]+[1]でセルの書式設定ダイアログボックスを表示します。
表示形式で　「*2012/3/14」　を選択しました。
8桁の数値を日付に変換できました。

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

ここでいろいろな関数を使って日付データへ変換していますが、上記の「区切り位置」で日付データへ変換するのが最も有効であるのがわかります。
DATEVALUE関数で日付形式の文字列は変換することもできます。
しかし、一部の文字列は変換できません。
=TEXT(A2:A6,"0000!/00!/00")*1　と文字列でも数字でも8桁の数値を日付に変更できます。
A6セルのように、ドットで区切られている　「20025.5.25」　というケースでは　ドットをスラッシュに変えることでExcelが日付と認識できます。
よって、　=SUBSTITUTE(A2:A6,".","/") として、変換しますが、これは文字列なので、DATEVALUE関数で日付データにします。
　=DATEVALUE(SUBSTITUTE(A2:A6,".","/"))　とします。
または、=SUBSTITUTE(A2:A6,".","/")*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と表示されます。
  (この時の年はパソコンのカレンダー(日付)の年を取得します。)

スポンサードリンク

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

　PageViewCounter
　 Counter
　Since2006/2/27