Excel日付に関する疑問

質問

「06/08/2015 05:17 am」このように書かれた日付+時間情報があります。日本では年/月/日が基本の為、このように月/日/年の順番に入力してもシリアルには変換できません。
年/月/日と入力すると、月/日/年の表に表示されるだけのようです。

「06/08/2015 05:17 am」のように入力されている値をシリアルに直すのに、セルの書式設定でする方法はありませんか。Date関数を使ったり、「区切り位置」で変換できるのは分かっております。

回答No.7

MackyNo1 · ベストアンサー

>「06/08/2015 05:17 am」のように入力されている値をシリアルに直すのに、セルの書式設定でする方法はありませんか。

以下のような手順で簡単にシリアル値に変換できます。

コントロールパネルの「地域と言語」の形式タブで形式を「英語(米国）」にしてから、文字列日付の時刻の列を選択して「データ」「区切り位置」で「完了」してください。

お礼

わお！これが私がイメージしておりました回答です。
書式設定で絶対にできるはずと思っておりました。
大変ありがとうございました。大変勉強になりました。

回答No.6

bunjii

>年/月/日と入力すると、月/日/年の表に表示されるだけのようです。
OSの設定でタイムゾーンが日本（大阪、札幌、東京）になっているときはExcelで日時を入力するとき年/月/日 時:分 am/pmのように入力するとシリアル値として認識します。
しかし、月/日/年 時:分 am/pmと入力すると文字列として認識し、表示形式の操作ではシリアル値に変換できません。
年/月/日 時:分 am/pmのように入力して、月/日/年 時:分 am/pmと表示するには表示形式を [$-409]mm/dd/yyyy hh:mm AM/PM のように定義してください。
既に文字列としてセルに入力されている「06/08/2015 05:17 am」と言う値は関数等でシリアル値に変換しなければならないでしょう。
表示形式は数値をどのように表示するかを定義する目的のようです。

回答No.5

msMike

[No.4補足]へのコメント、
》 Gです。
ということで、「06/08/2015 05:17 am」は標準書式(G)のセルに入力された文字列であること分かっただけです。

》 これから何がわかるのですか?
聞きように依っては私が叱られているようにも感じられますので、「詰らないことをお尋ねして、済みませんでした」と謝っておきます。m(_._)m

ご参考までに申し上げておきますと、
「2015/6/8 5:17 am」をセル A1 に入力すると、「2015/6/8 5:17」と表示されます。
この A1 を以下の書式設定を施すと「06/08/2015 5:17 AM」と表示されます。
mm/dd/yyyy h:mm AM/PM
そのとき、式 =CELL("format",A1) の戻り値は D1 を呈します。
ひょっとして、貴方の場合、書式設定で「06/08/2015 05:17 am」と表示されているのではないかと、単に確認したかったという次第。失礼しました。

お礼

叱っているように感じてしまったようで大変申し訳ございません。
これからもよろしくお願いします。

回答No.4

msMike

「06/08/2015 05:17 am」のように入力されているセルを A1 としたとき、
式 =CELL("format",A1)
の戻り値を教えてください。

補足

Gです。これから何がわかるのですか?

回答No.3

PXU10652

「06/08/2015 05:17 am」のように入力されている値をシリアルに直すのに、セルの書式設定でする方法はありませんか。
　文字列である以上、それはシリアル値ではないので、関数を使わずに日付表示させることはできません。やはり、関数で「=DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))」とするしかないのでは？

お礼

わかりました。不可能なのですね。初心者でも簡単に変換できる方法があればと思いました。

回答No.2

kagakusuki

何故、セルの書式設定でやらねばならないのでしょうか？

>Date関数を使ったり、「区切り位置」で変換できるのは分かっております。

という事であれば、使用していない適当なセルに「月/日/年の順番に入力されている文字列」をシリアル値のデータに変換する関数を入力しておいて、そのセルに表示される値をコピーしてから、元のセルに値のみを貼り付けた後、入力した関数を消去してしまえば済む話ではないでしょうか？
　例えばA1セルに 「06/08/2015 05:17 am」のように入力されている場合には、適当なセル(ここでは仮にZ1セルであるものとします)に

=IF(A1="","",IFERROR((MID(A1,7,4)&"/"&LEFT(A1,5))+RIGHT(A1,8),A1))

と入力しておき、Z1セルをコピーしてから、形式を選択して貼り付けの際に[値]を選択してA1セルに貼り付けを行い、その上でZ1セルの内容をDeleteすれば、A1セルの値をシリアル値に変換した事になります。

　また別の方法としては、入力されている日時が現在の年と同じ年である場合には、Excelの置換機能を使用して、「検索と置換」ダイアログボックスの[置換]タブの「検索する文字列」欄に「/2015」の様に年の部分を入力し、「置換する文字列」欄には何も入力されていない状態としたまま置換を行えば、「/2015」の部分が消えて「06/08 05:17 am」となりますから、「06/08 05:17 am」を入力した場合と同じ結果である「"今年の"06/08 05:17 am」に相当するシリアル値に変換されます。

回答No.1

aokii

まずは、シリアルに直さないと、セルの書式設定でする方法はありません。