- Home »
- エクセル関数の技 »
- 日付データの処理に関する技
更新:2024/3/8;作成:2007/9/17
- 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年から計算する」のチェックボックスをオンまたはオフにして切り替えることが出来ます。
- ただし、作成途中で切り替えると日付にズレを生じますので注意しましょう。
- セルに入力されている「日付」がシリアル値か否かはセルの表示形式を「標準」にします。
すると、2007年1月1日は「39083」という数値(シリアル値)になります。
- Excelはセルに入力されたデータ値を日付であると検知した場合、自動でシリアル値に変換します。
さらに、日付の表示形式を自動で適用してくれたりします。
- 1/1、1-1と入力すると2007年1月1日と判断し、39083に変換します。表示は1月1日となります。
そして数式バーには2007/1/1と表示されます。
(この時の年はパソコンのカレンダー(日付)の年を取得します。)
- 8桁の数値で表現された日付 「20050101」 を日付データに変換するには、TEXT関数を使うと便利です。
上図の例では =TEXT(A4,"0000!/00!/00")*1としています。
=TEXT(A4,"0000-00-00")*1 とすることもできます。
左の4文字が年、真ん中の2つが月、右の2文字が日となっているので、=DATE(LEFT(A4,4),MID(A4,5,2),RIGHT(A4,2)) とすることもできます。
- =TEXT(A4,"0000/00/00")*1 とスラッシュのみではうまくできませんので、感嘆符(ビックリマーク)をつけて=TEXT(A4,"0000!/00!/00")*1 とします。
「!」につづく1文字はそのまま表示されるので、日付の表示形式として利用できています。
最後の*1は日付の文字列を演算することでシリアル値に変換しています。
- 他のソフトからデータを取り込んだところ、日付が文字列のままであったりした時の処理方法です。
データ タブの区切り位置で日付データに変換します。
- 文字列のデータを選択します。
文字列のデータにするため各セルのデータは '2007/14/1のように、頭に '(アポストロフィ)を入力しています。
- データ タブのデータツールグループの[区切り位置]を実行します。
- 区切り位置ウィザードの1/3,2/3は[次へ]ボタンをクリックして、区切り位置ウィザードの3/3で「日付」にチェックを入れます。
表示先をC1にして、[完了]をクリックします。
- C列のように変換されます。値はシリアル値になっています。
Excel for Microsoft365では表示形式も勝手に適用されました。
DATEVALUE関数など関数を使って他のセルへ変換する
=DATEVALUE(日付文字列)
- DATEVALUE関数で変換することもできます。
しかし、一部の文字列は変換できませんでしたので、ほかの関数を使いました。
D4セルは =TEXT(A4,"0000!/00!/00")*1 と文字列でも数字でも8桁の数値を日付に変更できます。
D5セルは =SUBSTITUTE(A5,".","/")*1としています。
シリアル値に返還後は表示形式を短い日付形式にしています。
- ドットで区切られている 2004.1.1 というケースでは ドットをスラッシュに変えることでExcelが日付と認識できます。
よって、 =SUBSTITUTE(A5,".","/") として、変換しますが、これは文字列なので、DATEVALUE関数で日付データにします。
=DATEVALUE(SUBSTITUTE(A5,".","/")) としています。 =SUBSTITUTE(A5,".","/")*1 でもOKです。
- 日付同士を比較する時、セル参照では問題ないが値を数式に書くとうまくできないことがあります。
=A1>"2006/12/31"の場合、Excelが"2006/12/31"を文字列と認識するためです。
- 対処法の例
- "2006/12/31"*1 とか "2006/12/31"+0 としてシリアル値に変換して比較する。
- 文字列"2006/12/31"を"2006/12/31"*1のような計算式にすることで、Excelが日付と認識してシリアル値に変換して計算します。
- DATE関数を使ってシリアル値にして比較する。
- =A1>DATE(2006,12,31) として、日付データにして比較します。
引数をセルを参照する形式にするなら、 =A1>DATE(YEAR(B1),MONTH(B1),DAY(B1)) となります。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数の技 »
日付データの処理に関する技
PageViewCounter
Since2006/2/27