（エクセル）文字として扱われている日付を（日時順）に並べるようにする

最新のエクセル（クラウド）を使っています。

以下のような形で日付が文字として扱われているようで、日時順にきちんと並べ替えられません。
（午後の表示も２４時間表示ではなくPMで扱われています）

11/16/2017 9:07:33 AM
11/16/2017 8:48:50 PM
・・・・・・
・・・・
・・・・


どのようにすれば日時順に並び替える状態を作れますか？

よろしくお願いします。

質問者からの補足コメント

• 日付の桁数がバラバラです。
  月、日、時間が以下のように１桁だったり２桁だったりと厄介なことになっています。
  
  11/16/2017 9:07:33 AM　
  9/3/2017 10:32 PM

    補足日時：2017/11/19 00:06

No.7 ベストアンサー 回答者： tom04 回答日時： 2017/11/18 22:41

こんばんは！

この質問限定の関数（ユーザー定義関数）を作ってみてはどうでしょうか？

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）
普通のワークシート関数と同じ使い方をします。

Function myDT(c As Range) '//この行から//
Dim myY As Long, myD As String, myT As Variant, buf As String
myY = Mid(c, InStrRev(c, "/") + 1, 4)
myD = Left(c, InStrRev(c, "/") - 1)
buf = Replace(c, myD & "/" & myY, "")
myT = Left(Trim(buf), InStr(buf, "M") - 3)
If InStr(c, "PM") > 0 Then
myT = TimeValue(myT) + 0.5
End If
myDT = DateValue(myY & "/" & myD) + TimeValue(myT)
End Function '//この行まで//

A1セルからデータがあり、B列に表示するとします。
B1セル（セルの表示形式は好み「yyyy/m/d h:mm:ss」などにしておく）に

=myDT(A1)

としてフィルハンドルで下へコピー！

これでB列にシリアル値が表示されますので、
B列をキーに並び替えが可能です。m(_ _)m

この回答へのお礼

VBEとかよく分かりませんが、すごいですね。
ご指示通りやったら並び替えに成功しました。びっくりです。

本当にありがとうございました。

P.S.
この関数というやつは、別のファイルでも（=myDT）だけ入力すれば使用できるのでしょうか？それとも同じ手順を踏む必要があるのでしょうか？

お礼日時：2017/11/19 01:21

No.10 回答者： tom04 回答日時： 2017/11/19 08:43

No.7です。

＞P.S.
について・・・

お示ししたユーザー定義関数はそのブック内のみ有効です。
すなわち他のブックでその関数を使いたい場合は同じような方法で登録すれば使用可能です。

※　ブック保存時は「マクロ有効ブック」として保存します。m(_ _)m

この回答へのお礼

ありがとうございます。理解しました。

お礼日時：2017/11/19 15:07

No.9 回答者： GooUserラック 回答日時： 2017/11/18 23:14

No.4 の修正（コピペの際「=」が抜けていました）すみませんでした。

「Ｅ列に「LEN(A1)」を入れます」は「Ｅ列に「=LEN(A1)」を入れます」に訂正してください。

この回答へのお礼

大丈夫です。
わざわざありがとうございます。

お礼日時：2017/11/19 01:29

No.8 回答者： d-q-t-p 回答日時： 2017/11/18 22:51

開いている PCの問題ですから 言語設定を変えてやれば正常に

日付として認識します。

コントロールパネルの [地域と言語]で 形式を「英語(米国)」に
して「適用」をクリック

この状態で 列を参照して [データ]→[区切り位置]→[完了]を実
行すれば シリアル値に変換できます。

後は 形式を「日本語(日本)」に戻して[OK]でいいはずです。

この回答へのお礼

なるほど。そういうやり方もあるのですね。
マックなのでコントロールパネルはありませんが、設定から似たようなことを試して見ます。ありがとうございました。

お礼日時：2017/11/19 01:26

No.6 回答者： mike_g 回答日時： 2017/11/18 22:22

米国式日付形式文字列を日本語版 Excel に使用可能にするための変換式を考えてみました。

添付図参照
B1: =(MID(A1,FIND("/",A1,4)+1,FIND(" ",A1)-FIND("/",A1,4)-1)&"/"&REPLACE(A1,6,5,""))*1 書式は yyyy/m/d h:mm:ss AM/PM
C1: =B1　書式は m/d/yyyy h:mm:ss AM/PM

この回答へのお礼

ありがとうございます。
並び替えるを行うと日付でソートされずに数字を見てソートされているようなので、そこがクリアにナルト嬉しいです。

（またB１はそのままペーストすると＃Value!が出ます。最後にある*1を削除すると表示されますが、*1は不要なのでしょうか？）

お礼日時：2017/11/19 01:10

No.5 回答者： GooUserラック 回答日時： 2017/11/18 21:50

No.4 について

この手の物は変数を使えば簡単なんですが、使えないのでＢ～Ｆ列を変数として使っています。（１つの式で作る強者もいますが僕はそのタイプではありません）
Ｂ列は１つ目の「/」の位置です。
Ｃ列は２つ目の「/」の位置です。
Ｄ列は「 」の位置です。
Ｅ列はＡ列の文字数です。
Ｆ列はそれぞれを使って「月」「日」「年」「時刻」に、それぞれ分けて変換しています。（ここも、それぞれの項目に分けた方が判りやすいかもしれないけど、どうにかおさまるレベルなのでまとめちゃいました。幸いだったのは時刻の方はそのまま認識してくれたので助かりました）

この回答へのお礼

解説頂いて助かります。後でゆっくり自分でも消化して使おうと思います。
とにかく初めから最後までありがとうございました。

お礼日時：2017/11/19 00:14

No.4 回答者： GooUserラック 回答日時： 2017/11/18 21:35

２行追加でも出来るとは思うのですが、訳が分からなくなるので分割します。

例えば元の列がＡ列とした場合右隣に６列挿入します（Ｂ～Ｇに空欄が出来るはず）
Ｂ～Ｆ列の書式設定を「標準」にしておきます
Ｇ列の書式設定を「yyyy/mm/dd h:mm:ss」などにしておきます
Ｂ列に「=SEARCH("/",A1)」を入れます
Ｃ列に「=SEARCH("/",A1,B1+1)」を入れます
Ｄ列に「=SEARCH(" ",A1)」を入れます
Ｅ列に「LEN(A1)」を入れます
Ｆ列に「=DATE(MID(A1,C1+1,D1-C1-1),LEFT(A1,B1-1),MID(A1,B1+1,C1-B1-1))+TIMEVALUE(MID(A1,D1+1,E1-D1))」を入れます
Ｆ列をコピーしてＧ列に「値」貼り付けします。
Ｇ列に問題が無ければＡ～Ｆ列を削除します。

この回答へのお礼

すごいですっ！（ここまで解決してしまうとは！驚きと感動です）
他人のためにここまでしていただいて本当にありがとうございます。

１点だけ分からないのですが、
＞Ｇ列の書式設定を「yyyy/mm/dd h:mm:ss」

これは、セルの書式設定＞ユーザー設定＞種類をyyyy/mm/dd h:mm:ss
を選択すれば良いのでしょうか？
　　　　↓
そのようにすると
　　　　↓
2017/10/19 0:00:00　と時間が0:00:00と表示されてしまいます。

お礼日時：2017/11/18 23:03

No.3 回答者： GooUserラック 回答日時： 2017/11/18 20:56

もしかしたら、そうなるのでは？と実は思っていました。

結構面倒なので時間がかかりそうです。他の方の回答を待つか、お待ちください。

この回答へのお礼

GooUserラックさん

すいません。かなり大変そうですよね。無理しなくてもいいですよ。（大変ならば諦めますので）

だいたい、これできる人はいるんですかね。（月も日も時間も１桁だったり、２桁だったりしますもんね。おまけにAM PM表示が並び替えの際にどう判断するのかも私にはさっぱり。）

色々やっていただいて感謝してます。
本当にありがとうございます。

お礼日時：2017/11/18 21:26

No.2 回答者： GooUserラック 回答日時： 2017/11/18 20:33

それでしたら、以下のような形で文字列からシリアル値に変換されたら良いのでは？

例えば元の列がＡ列とした場合右隣に２列挿入します（B･Cに空欄が出来るはず）
Ｂ列の書式設定を「標準」にしておきます
Ｃ列の書式設定を「yyyy/mm/dd h:mm:ss」などにしておきます
Ｂ列に「=DATE(MID(A2,7,4),LEFT(A2,2),MID(A2,4,2))+TIMEVALUE(MID(A2,12,11))」を入れます
それをコピーしてＣ列に「値」貼り付けします。
Ｃ列に問題が無ければＡＢ列を削除します。

この回答へのお礼

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

B列に上記をペースとしたら、”#value!”という表示が出ました。
これって、私の説明不足でしたが、日付の表示の桁数が日や時間によって違うことと関係しますか？

11/16/2017 9:07:33 AM　
9/3/2017 10:32 PM

どのように処理したら良いでしょうか？
（甘えてばかりですいません。）

どうぞよろしくお願いいたします。

お礼日時：2017/11/18 20:52

No.1 回答者： GooUserラック 回答日時： 2017/11/18 19:56

なんとなくですが書式設定が「mm/dd/yyyy h:mm:ss AM/PM」のようになっているのでは？「yyyy/mm/dd h

:mm:ss」ように変えればよいのでは？

この回答へのお礼

エクセルの知識があまりありませんが、表示形式を（日付）に変えて色々な（種類）を試しましたが、全く表示に変更がなされません。

（早速の回答ありがとうございます。）

お礼日時：2017/11/18 20:09