【Excel Tips】Accessなどの日付データをExcelの日付に変換するには？

概要

　Accessやデータベースの日付データは、Excelで扱いたい表示形式と違う場合も多いですよね。例えば「2008年8月2日」の場合、「20080802」または「080802」と入力されている場合が多いです。このデータを一つ一つについて、年月日を区切るためにスラッシュ（/）を挿入するのは大変な作業になります。

　そこで、セルの書式設定で表示形式を「日付」にしてみるとどうなるでしょうか。残念ながら、この場合は表示が「########」となってしまい、思うような結果は得られません。

［書式］-［セル］を選択

［セルの書式設定］ダイアログボックスで、表示形式を「日付」に変更すると...

「########」と表示される

　では、20080802と入力された値の表示形式を変更しても思うような表示が得られないのはなぜでしょうか。これは、Excelでの日付は1900年1月1日が「1」ではじまるシリアル値で管理されていることによります。

　2008年8月2日をExcelのシリアル値で表すと39662となります。よって、数値の指定によって2008年8月2日という表示を得たい場合は、39662を入力しなければなりません。

　ところで、20080802をExcelの日付のシリアル値として想定すると、1年を365.2422日として計算した場合、西暦56879年4月28日になります。Excelでの日付は9999年12月31日までしか扱うことができないので、その数値を日付に表示形式を変更しても「######」表示になるのです。

　この問題を解決するには、文字列操作関数と日付関数を使って、正しいシリアル値を取得する必要があります。ここでは、文字列操作関数であるLEFT、MID、RIGHTを利用して年、月、日の値を取得し、DATE関数を使って取得した対応するシリアル値を取得します。

　20080802の場合、最初の4桁は年、次の2桁が月、最後の2桁を日として年月日を抜き出します。年はLEFT関数、月はMID関数、日はRIGHT関数を利用し、それぞれDATE関数の「年」「月」「日」に代入します。

操作手順

　A1からA3までの3パターンの日付を、それぞれ右のセルに求めます。

B1セルの場合

　元の日付が「20080802（YYYYMMDD）」の場合の、Excelの日付データへの変換方法を説明します。

　DATE関数の「年」「月」「日」の引数は、以下のように指定します。

• 年：A1の最初の4文字を抜き出すので LEFT(A1,4)
• 月：A1の5文字目から2文字を抜き出すので MID(A1,5,2)
• 日：A1の最後の2文字を抜き出すので RIGHT(A1,2)

［関数の引数］ダイアログボックス：B1セルの場合

　B1セルの数式は以下の通りです。

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

B2セルの場合

　元の日付データが「080802（YYMMDD）」の場合の、Excelの日付データへの変換方法を説明します。

　DATE関数の「年」「月」「日」の引数には、以下のように指定します。

• 年：A2の最初の2文字を抜き出し、2000を加算するので LEFT(A2,2)+2000
• 月：A2の3文字目から2文字を抜き出すので MID(A2,3,2)
• 日：A2の最後の2文字を抜き出すので RIGHT(A2,2)

［関数の引数］ダイアログボックス：B2セルの場合

　B2セルの数式は以下の通りです。

=DATE(LEFT(A2,2)+2000,MID(A2,3,2),RIGHT(A2,2))

B3セルの場合

　元の日付データが「080802」であるものの、文字列ではなく数値として「80802」と処理されてしまっている場合の、Excelの日付への変換方法は以下の通りです。

　DATE関数の「年」「月」「日」の引数には、以下のように代入します。

• 年：A3の最初の1文字を抜き出し、2000を加算するので LEFT(A3,1)+2000
• 月：A3の2文字目から2文字を抜き出すので MID(A3,2,2)
• 日：A3の最後の2文字を抜き出すので RIGHT(A3,2)

［関数の引数］ダイアログボックス：B3セルの場合

　B3セルの数式は以下の通りです。

=DATE(LEFT(A3,1)+2000,MID(A3,2,2),RIGHT(A3,2))