EXCELの数値から年月への変換

質問

EXCELで日付（年月）が数値で入力されているものがあります。
年月へ書式変更すると10月だけ上手く行きません。
良い方法はないでしょうか？

例）変換前　2016.10　（書式 "0.00"）
　　　↓
　　変換後　1905年7月　（書式 ”yyyy年mm月”）
以上

回答No.2

kagakusuki · ベストアンサー

変換前の数値が入力されているセルが例えばA1セルであった場合には、変換後の日付を出力するセルに対して、セルの書式設定の表示形式を[ユーザー定義]の

yyyy"年"mm"月"

に設定した上で、次の様な関数を入力されると良いと思います。

=IF($A1="","",IFERROR(TEXT(SUBSTITUTE(IF(LEFT(CELL("format",$A1),1)="F",TEXT($A1,"0."&REPT(0,MID(CELL("format",$A1),2,2))),$A1&""),".","/"),"yyyy年mm月")+0,"変換不能"))

お礼

回答ありがとうございました。
ご提示いただいた数式で希望通りの変換ができる様です。
数式の内容を理解できる様に詳しく見てみたいと思います。

回答No.4

mdmp2

「2016.10」を関数を使って、別のセルに「2016年10月」に変換するのは簡単ですが、現在入力されているセルの書式をいじるだけで　「2016年10月」と表示させることはできないと思います。

一つの方法は「置換」です。
ただし、10月の置換は一筋縄ではいきません。
2016.01
2016.02
2016.03
・
・
・
2016.09
2016.10
2016.11
2016.12

と入力されているとします。
2016.10 は、書式を 0000.00 に設定して表示させていますが、数値は2016.1 です。

これらのセルを選択して、置換ウイザードを開き、「.」を「/」に置換します。
すると、これらの数値は「年月」として入力されたものとなり、
書式を yyyy年mm月 に設定すると、
2016年01月
2016年02月
2016年03月
・
・
・
と表示されますが、10月だけは、1月になってしまいます。

それを避けるために、まず、2016.10 だけを先に置換します。
選択範囲は対象セル全体でOK です。

置換ウイザードで、2016.1 を2016/10 に「すべて置換」します。
置換前を 2016.10 にすると、検索にヒットしません。
そのとき、「セル内容が完全に同一であるものを検索する」にチェックを入れる必要があります。さもないと、2016.11 も、2016.12 も2016年10月 になってしまします。
2015.10 や、2014.10 があればそれらについても 2015/10 または、2014/10 に置換します。

つぎに対象セル全体を範囲選択して、
「.」を「/」に「すべて置換」します。

この2回の操作（2015年、2014年のデータがあれば、4回の操作）ですべて、xxxx年xx月 になります。
少々面倒な操作ですが、データ数がたくさんあるときはやってみる価値があると思います。

10月を置換するまえに「.」を「/」に「すべて置換」すると、1月と10月の区別がつかなくなってしまいますので注意してください。やってしまったら、「元に戻す」で戻ります。

回答No.3

bunjii

>年月へ書式変更すると10月だけ上手く行きません。
例に提示の　2016.10　と言う値は2016年10月のことですか？
月だけでなく西暦の年も書式設定の変更では無関係の年月として表示されるはずです。

>良い方法はないでしょうか？
A1セルに2016.10と入力されている値を2016年10月1日のシリアル値に変換してから書式をを"yyyy年mm月"の指定しないといけません。
関数で変換するにはDATE関数を使うと確実です。
=DATE(A1,MOD(A1*100,100),1)
西暦年月をキーボードで入力するときは2016/10のようにタイプしてEnterキーで確定すると日付のシリアル値に変換されますが、2016.10と入力してEnterキーで確定すると数値の2016.1と言う値になります。
これはExcelの処理ではプログラム上の規定事項なので変えられません。
日付のシリアル値とは1900/1/1を1とする経過日数なので2016.1を日付の書式で表示すると1905年7月8日になります。
2016年10月1日のシリアル値を「標準」の表示形式にすると42644と言う数値であることが分かるでしょう。
この機会にExcelで扱う数値データの表示形式について知識を広めてください。
例えば時刻は1日（24時）を1とした小数であり表示形式で12:25:15のように変化させることができることも知っておくと良いでしょう。

回答No.1

neKo_quatre

> 10月だけ上手く行きません。

そもそも、他の月日でも上手く行かないと思いますが。
逆に、どういう日付なら上手く行ったんでしょう？

書式どうこうでは無理だと思うので、
A1セルに「2016.1」という数値が入っているとして、
B1セルに、=DATE(INT(A1),MOD(A1*100,100),1)
B1セルの書式を「yyyy年mm月」
とか。