エクセルの日付表示

質問

エクセル2000です。例えば　19720105と入力されているセルを、最終的に昭和47年1月5日と表示するのに、まず、1972/01/05と、表示することから始めてるのですが、セルの表示形式でyyyy/m/dにすると、エラー表示の########というような　表示になります。たとえば　手打ちで、日付け表示のところに/を入れることもできるでしょうが、数が莫大でできません。なにか　関数や表示形式で、たとえば今日なら20030601　の表示を平成15年6月１日と表示できる方法を教えてください。よろしくお願いします。

回答No.5

tbobi · ベストアンサー

＃２、＃４です。何度もすみません。

日付のところが間違ってました。RIGHTにするつもりがLEFTにしてました。

正しくはこちら。
↓

A1に　20030601　が入っている場合

別のセルに

方法１　次の式をいれ、表示形式を和暦の表示に変える
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

方法２　次の式を入れる
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"ggge年m月d日")

これだけなのもどうかと思うので、少し＃４の補足をします。

2003万601日目というのはどれぐらいなのかというと、
単純に１年３６５日として数えても、54878年を超えます。1が1900/1/1なので1900をそれに足すと　西暦56778年となります（細かく言うとうるう年が何度か入るのでもうちょっと少なそうですけどね）

なお、excelが持っている日付の最大値は9999/12/31のようです。（EXCEL2002で確認）この日のシリアル値は、2958465　となります。

また、その翌日の10000/1/1は入力はできますが、表示形式を標準にしてもシリアル値にはならないようです。

あまり本題とは関係ない話でしたが、ご参考まで。

お礼

ご丁寧に懇切丁寧に教えてくださりありがとうございました。この方法で　日付表示ができました。だから、結局、ユーザー設定では2003/6/1という表示は無理なんですよね　よく分かりました。今後も　よろしくお願いします。
ありがとうございました。

回答No.4

tbobi

＃２です。

＞セルの幅を広げても、同じエラー表示になります

確認してみました。
そのセルにマウスを近づけると、
「負の日付または時間は########と表示されます」
などと出てきました。

これは日付として認識できる範囲を超えているときに表示されるようです。excelは内部で日付・時刻をシリアル値として持っていて、「シリアル値１」＝「１日」となっています。

シリアル値１が1900/1/1（バージョンもしくは設定によって異なる）ですから、20030601と入れた場合、その日から数えて2003万601日目を表示しろとexcelに言っているわけです。それはexcelが認識する範囲を超えているのでこういう表示になるようです。

セル幅が足りないときも同じ表示になるので、勘違いしてしまいました。すみません。

その########となってしまうセルは日付・時刻以外のもの（標準、数字、文字列など）にして、別のセルに数式を入れて＃２の方法でやってください。

あとは、ご希望によって、形式を選択して貼り付けで値に直せばOKだと思います。

回答No.3

imogasi

既出の回答とおなじですが、空き列に
=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))
とＭＩＤ関数で統一して出来ます。
書式－セル－日付け－平成９年３月４日をクリックする。
当然１－９日、月は０１、０９などと統一された入力に
なっていないとだめです。
列幅は充分取っておいてください。

お礼

ありがとうございました。よくわかりました。どうも

回答No.2

tbobi

＞エラー表示の########というような　表示になります

セル幅に入りきれないからそういう表示になります。
えらー表示ではないですよ。セル幅を広げてみてください。

A1に　20030601　が入っている場合

方法１　次の式をいれ、表示形式を和暦の表示に変える
=DATE(LEFT(A1,4),MID(A1,5,2),LEFT(A1,2))

方法２　次の式を入れる
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),LEFT(A1,2)),"ggge年m月d日")

という方法が考えられます。

補足

お答えありがとうございます。エラー表示の件ですが、セルの表示形式のユーザー設定のところで　yyyy/m/d　を選ぶと、############となると質問しましたが、セルの幅を広げても、同じエラー表示になります。2003/06/01とセルの表示形式にするには、どうすればそのような表示になるか　ご存知でしょうか？？よろしくお願いします。

回答No.1

Hageoyadi

数式は
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
ですか。
で、セルの表示形式を「平成・・・」に変えると。

お礼

すぐに教えていただきありがとうございました。よく分かりました。また、よろしくお願いします。