エクセルでの日付表示について

質問

エクセル（2010）で日付の表示変更の方法についてお尋ねします。

以下の仕様の日付データがあります。

[07/02/12 09:43:00 午後]　　　月/日/年　時間　午前or午後　※区切りは半角スペース

これを
[12/07/02 21:43:00] 　年/月/日　時間（24時間表示）

に変更したいと思っています。

単純に”午後”の文字列を削除すると
[2007/02/12 09:43:00]
となり、月/日/年の順番が間違って表示されます。
セルの表示形式のどの仕様を選択しても同じ結果でした。

文字列の削除後、日付が希望通りに変更する方法を教えていただけますでしょうか。
（24時間表示はこだわりません）

よろしくお願いいたします。

回答No.3

chonami · ベストアンサー

つまり、元データは文字列なのですね。
察するに別のソフトからのデータでしょうか。

ちょっと手間を掛けますが、まず対象セル（か列）を選択→データ→区切り位置→次へ→次へ→日付の列が選択されていると思うので、列のデータ形式：日付のプルダウンから「MDY」を選択→完了

これで、日付と時間と午前/午後の3つのセルに分かれます。

午前/午後の列を選択しCtrl+Hの置換ダイアログで、午前を0に、午後を12：00に置換します。

もし、A、B、C列にデータを分けたとしたら、どこかのセルに=SUM(A1:C1)とすると、そこに日付のデータが入ります。（見た目は日付のみですが時間のデータも含まれています。）

そのセルの表示形式を「yy/mm/dd hh:mm:ss」とする。
（必要ならコピーして値貼り付けする。）

では如何でしょうか？

関数で一発処理の回答も付くかもしれませんが・・・

お礼

回答いただきありがとうございます。

別ソフトからのデータです！

区切り位置指定の機能に目からウロコでした。活用頻度大です！

回答No.10

tom04

こんばんは！
データは
[ ]はなくて
07/02/12 09:43:00 午後
となっている前提です。

A1セル以降にデータがあるとし、B列に表示するとします。

B1セル（セルの表示形式は「日付」から　時刻込みの表示形式にしておきます）に
データ内には必ず　午前　または　午後　があるという前提で・・・

=IF(ISNUMBER(FIND("後",A2)),SUBSTITUTE(A2,"午後","")*1+"12:00",SUBSTITUTE(A2,"午前","")*1)

という数式を入れオートフィルで下へコピーではどうでしょうか？
（シリアル値が表示されます）m(_ _)m

お礼

おはようございます。関数を教えていただきありがとうございます。

午前、午後の表記変更について参考にさせていただきます。

回答No.9

K Kazz（@JazzCorp）

サンプルが2012/7/2だとして、
=DATEVALUE(MID($A1,7,2)&"/"&LEFT($A1,5))+TIMEVALUE(MID($A1,10,8))+IF(RIGHT($A1,2)="午後",0.5,0)

お礼

関数を教えていただきありがとうございます。
一発変換でした。
DATEVALUE、TIMEVALUEの使い方に関しても参考にさせていただきます。

回答No.8

okormazd

=(MID(A1,7,2)&"/"&LEFT(A1,5)&MID(SUBSTITUTE(SUBSTITUTE(A1,"午前","AM"),"午後","PM"),9,9))*1
とか、
=(MID(A1,7,2)&"/"&LEFT(A1,5)&MID(A1,9,9))*1+IF(ISERR(FIND("午前",A1)),0.5,0)
とか。

お礼

関数を教えていただきありがとうございます。
[ISERR]関数の使い方についても参考にさせていただきます。

回答No.7

KURUMITO

回答No6です。
B1セルの表示形式は「ユーザー定義」で種類の窓には　yy/mm/dd hh:mm:ss　と入力します。

お礼

関数を教えていただきありがとうございます。
B1セルの表示形式についてうっかりしていました。設定後一発変換でした。
参考にさせていただきます。

回答No.6

KURUMITO

A1セルにお示しのデータが入力されているとして、B1セルには次の式を入力してセルの表示形式を時刻から選択すればよいでしょう。

=DATE(MID(A1,7,2)+2000,LEFT(A1,2),MID(A1,4,2))+TIMEVALUE(MID(A1,9,8))+IF(ISERROR(FIND("午後",A1)),0,0.5)

回答No.5

chayamati

セルのコピペは書式も含めて複写されます。
せっかくのユーザー定義がコピー元の書式になった結果です。

書式以外のコピペは貼り付け先のセルを
［右クリック］→［形式を選択して貼り付け］→［数式］

お礼

回答いただきありがとうございます。

No1のFEX2053さんのお礼内容の手順3についてでしょうか。

1　空セルの書式設定をユーザ定義yyyy/mm/dd hh:mm:ssに定義。元データ[07/02/12 09:43:00　午後]のセルをコピーして［右クリック］→［形式を選択して貼り付け］→［数式］で空セルに貼り付け
　　　→　変更なし
2　"午後"の文字列を削除
　　　→　[2007/02/12 09:43:00]と表示
3　[07/02/12 09:43:00]の数値のみを貼り付け
　　　→　[2007/02/12 09:43:00]と表示

頑固です。

回答No.4

shintaro-2

通常”午後”という文字列が入った段階で、属性が日付から文字列になります。

お考えのことを実現する場合は

　2012/7/2 21:43
　あるいは
　2012/7/2 9:43 PM
と入力しておく必要があります。

この状態で
　　書式設定でユーザー定義を選択し、
　　　mm/dd/yy hh:mm とすれば、お考えの表示は可能です。

お礼

回答いただきありがとうございます。

元データの日付が2012/7/2 9:43 PMの形ではないとだめなんですね。確かに、07/02/12の順番でどの数値が年を指しているかの判断は難しいですね。

回答No.2

pirorin_no1

セルの書式設定で、「種類」をリストから選択ではなく、
欄にそのまま「yy/mm/dd hh:mm:ss」
または「yy/mm/dd hh:mm:ss AM/PM」と書き換えてみてはどうでしょう？

お礼

回答いただきありがとうございます。

以下の結果となりました。
1 元データセル[07/02/12 09:43:00 午後]の書式設定をユーザ定義yy/mm/dd hh:mm:ssまたはyy/mm/dd hh:mm:ss AM/PMに欄へ直接書き入れ変更
　→変更せず
2　1の手順後、”午後”の文字列を削除
　→セルには[07/02/12 09:43:00]と表示しましたが、数式バーには[2007/02/12 9:43:00]と認識されていました。（AM/PMの場合も同様)

回答No.1

FEX2053

どぞ。
http://www.eurus.dti.ne.jp/yoneyama/Excel/user_set.htm

データが日付シリアルなら、セルの書式設定で
ユーザー定義を選び、

yyyy/mm/dd hh:mm:ss

で済む話なんじゃないですか？

お礼

早速お答えいただきありがとうございます。
元データのセル書式は「標準」です。

以下の方法を試しました。
1 元データセル[07/02/12 09:43:00 午後]の書式設定をユーザ定義yyyy/mm/dd hh:mm:ssに変更
　→変更せず
2　1の手順後、”午後”の文字列を削除
　→[2007/02/12 09:43:00]と表示
3　空セルの書式設定をユーザ定義yyyy/mm/dd hh:mm:ssに定義。元データ[07/02/12 09:43:00]をコピーして空セルに貼り付け
　→[2007/02/12 09:43:00]と表示

うまくいきませんでした・・。