8桁表示の日付をシリアル値に変換
「20101231」という8桁で表示された日付をExcelで使える形式、シリアル値に変換する方法をまとめておきます。
=DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100))
DATE関数を使い、年、月、日をそれぞれ代入すれば、シリアル値に変換できます。
年は10000で割って切り捨て。
月は100で割って切り捨て、さらに100で割った余り。
日は100で割った余り。
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
8桁の数値を文字列としてとらえる方法です。
年は左から4文字。
月は左から5番目から2文字。
日は右から2文字。
=VALUE(TEXT(A3,"0000!/00!/00"))
TEXT関数を使い表示形式を変える方法です。
「20101231」を「2010/12/31」という形式に変えます。
これを数値に変換すればExcelでは日付として認識します。
=VALUE(TEXT(A4,"00!/00!/00"))
応用として「101231」という6桁で表示された日付を変換します。
Twilogでは日付がこの形式で出力されます。
一見すると「10/12/31」という形式になりそうですが、Excel(2007)では年の部分について次の通り解釈します。
「00」から「29」までは、2000年から2029年まで。
「30」から「99」までは、1930年から1999年まで。
この仕組みにより、問題なく変換されます。心配ならば先頭に「20」を付加してもよいでしょう。
なお「29」を2029年と解釈するところはWindowsで設定を変えられます。
XPの場合、コントロールパネルの「[地域と言語のオプション」を探すと「2桁の数字で年を入力すると、次の範囲内での暦年として解釈されます」という箇所があります。
Windows7の場合、「地域と言語」に同様の箇所があります。
[ 2010年12月23日 | カテゴリー: Excel | タグ: 日付 ]
« 任天堂の卓上カレンダー、2011年版 | VBA関数「split」をワークシートで使う »
コメントを残す