Excel 英字含む日時→数字日時への変換方法

質問

以下のような1セルに英字を含んだ日時データを数字のみの日時データに変換する方法は
ありますでしょうか？複数の方法があればあるだけ教えていただけると助かります。

例：
Fri Nov 2 2012 9:00 PM

　　　　↓

2012/11/02 21:00
※変換後のセルに曜日は含まなくて良いです。

回答No.8

keithin

失敬、間違いました。

＞秒を含む

あと３文字足さないとダメでした。
=IF(A1="","",(MID(A1,9,2)&" "&MID(A1,5,3)&" "&MID(A1,11,5))+TRIM(RIGHT(A1,11)))

失礼しました。

お礼

keithinさんご回答ありがとうございます。ご回答の部分でお手数おかけしてしまい申し訳ありませんでしたMID関数の文字抽出数に執着してしまい前に進みませんでした、RIGHT関数の部分だったんですね。

なるほど、No.7の形式だと11時の時は1文字ずれて正しく出なくなってしまいますね、お知らせ頂きありがとうございます。重ねて感謝申し上げます。早く覚えられるよう精進致します。

回答No.7

keithin

＞秒を含む

=IF(A1="","",(MID(A1,9,2)&" "&MID(A1,5,3)&" "&MID(A1,11,5))+TRIM(RIGHT(A1,10)))

＃補足
あなたがご自分でどうやってみて失敗したのかキチンと補足として情報提供してくれれば、どこを間違えているからこう考えますと、もっと役に立つ回答も出来ますが。
単に「できません」の丸投げなので、また回答オンリーしか答えられません。ありがちな状況としては、似たり寄ったりの回答が多数寄せられてすっかり混乱しているのかもしれませんね。フォローはしませんが応援はしていますので、頑張って理解してください。

回答No.6

WindFaller

こんにちは。

私も、せっかくだから、VBAで回答します。
数式を検討してみましたが、あまり大した違いが出ませんでした。
コンパネをいじる方法があるような気がしましたが、それはやめます。

そもそも、曜日を取り去り、"Nov 1 2011 9:00 AM" は、日付の位置だけ交換し、" 1 Nov 2011 9:00 AM" とすれば、そのまま日付認識します。本来は、以下のようなユーザー定義関数ではなく、そのままマクロで置換してしまうのが早いような気がします。

ご質問の趣旨は、そういう問題ではなさそうなので、あえて、掲示します。みなさんの作ったものとは、作業は同じですが、異質なものです。

こんなものを作っているのは、あくまでも、長い間VBAをやっていなかったので、トレーニングです。

以下のユーザー定義関数は汎用性があります。

'例
Fri Nov 1 2012 9:00 AM
Fri 1 Nov 2012 9:00 AM
Nov 1 2012 9:00 AM
"2012/11/01"　←これは意味がありませんが、同じようにしてみました。
"1/10/2012" 　←これは、アメリカ方式に認識するようです。

'//標準モジュール
Public Function reDateVal(arg As Variant)
Dim Re As Object
Dim arDat As Variant
Dim arPat As Variant
Dim i As Long
Dim pat0, Pat1 As String, Pat2 As String, Pat3 As String, Pat4 As String, Pat5 As String
Dim r As Variant, r1 As Variant, r2 As Variant
r1 = "": r2 = ""
On Error Resume Next
　 r1 = DateValue(arg)
　 r2 = TimeValue(arg)
　 If IsDate(r1) Then GoTo Express
On Error GoTo 0
pat0 = "([0-3]{1,2})[^\d]" '月の数字(予備）
Pat1 = "[a-z]{3}" '月の文字
Pat2 = "19\d\d|20\d\d" '年
Pat3 = "(\d{1,2})[\s]*" '日付
Pat4 = "\d{1,2}:\d{2}"　'時間
Pat5 = "am|pm"
arPat = Array(pat0, Pat1, Pat2, Pat3, Pat4, Pat5)
arg = StrConv(Trim(arg), vbLowerCase + vbNarrow)
ReDim arDat(UBound(arPat))
With CreateObject("VBScript.Regexp")
　.Pattern = "mon|tue|wed|tue|fri|sat|sun"
　Set r = .Execute(arg)
　If r.Count > 0 Then
　　arg = .Replace(arg, "")
　End If
　For i = 0 To UBound(arPat)
　　.Pattern = arPat(i)
　　Set r = .Execute(arg)
　　If r.Count > 0 Then
　　　arDat(i) = r.Item(0).Value
　　　If r.Item(0).SubMatches.Count > 0 Then
　　　　arDat(i) = r.Item(0).SubMatches(0)
　　　End If
　　End If
　Next i
End With
On Error Resume Next
r1 = DateValue(arDat(2) & "-" & arDat(1) & "-" & arDat(3))
If r1 = "" Then r1 = DateValue(arDat(2) & "-" & arDat(0) & "-" & arDat(3))
r2 = TimeValue(arDat(4) & " " & arDat(5))
On Error GoTo 0
Express:
reDateVal = r1 & " " & r2
End Function
'//

使い方：
= reDateVal(A1)
後は、書式で望む表示形式に変更

お礼

ご回答ありがとうございます！まだ初心者で深く理解はできておりませんが、こうやって関数と同じ結果がでるのだと断片的にですが見えます。少しずつ理解して自身で作れるくらいになりたいですね。

回答No.5

kagakusuki

ANo.4です。
　申し訳御座いません、間違えておりました。
　正しい関数は、以下の通りです。

=IF(ISNUMBER((MID(A1,9,2)&MID(A1,4,5)&REPLACE(A1,1,10,))+0),(MID(A1,9,2)&MID(A1,4,5)&REPLACE(A1,1,10,))+0,"")

或いは

=IF(A1="",(MID(A1,9,2)&MID(A1,4,5)&REPLACE(A1,1,10,))+0,"")

お礼

ご回答ありがとうございます！No.1さんご回答の関数だけではなくこのような方法もあるのですね、それぞれの関数を理解してぜひ利用したいと思います。

回答No.4

kagakusuki

元となる文字列がA1セルにあるものとした場合には、まず、変換後の日時データを表示させるセルに次の関数を入力して下さい。

=IF(ISNUMBER((MID(A1,5,2)&" "&LEFT(A1,4)&REPLACE(A1,1,6,))+0),(MID(A1,5,2)&" "&LEFT(A1,4)&REPLACE(A1,1,6,))+0,"")

　そして、そのセルの書式設定の表示形式を[ユーザー定義]の

yyyy/mm/dd h:mm

にして下さい。

　尚、入力されている文字列データが、必ず、御質問文中の例と同様の形式のデータとなっている事が保障されてる場合には、次の様な関数でも、同じ事が出来ます。

=IF(A1="",(MID(A1,5,2)&" "&LEFT(A1,4)&REPLACE(A1,1,6,))+0,"")

回答No.3

tsubuyuki

> 複数の方法があればあるだけ
との事でしたので、私からはあえてユーザー定義関数を（笑）。

Function DateConv(myStr As String) As Variant
Dim myYear As Integer, myMonth As Integer, myDay As Integer
Dim myTime As String
Dim EMonth As String
Dim AMon As Variant, BMon As Variant, i As Integer

　　AMon = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
　　BMon = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)

　　myYear = Trim(Mid(myStr, 11, 5))

　　EMonth = Trim(Mid(myStr, 5, 3))
　　　　For i = 0 To 11
　　　　　　If AMon(i) = EMonth Then
　　　　　　myMonth = Replace(EMonth, AMon(i), BMon(i))
　　　　End If
　　Next

　　myDay = Trim(Mid(myStr, 9, 2))

　　myTime = Trim(Right(myStr, 8))

　　DateConv = DateSerial(myYear, myMonth, myDay) + TimeValue(myTime)
End Function

これを標準モジュールに書いておくと、
　　=dateconv(A1)
のように、ワークシート上で普通の（？）関数として使えるかもしれません。

添付図では書式を「yyyy/m/d ddd h:mm AM/PM」にしていますが、
通常通りの「yyyy/mm/dd hh:mm」にももちろん対応します。

ただし、対象セルが「"Fri Nov 2 2012 9:00 PM"と言う文字列」じゃないとエラーが出ます。

踏まえて、わざわざここまでやる必要があるかどうかは、全く以て謎です（笑）。

お礼

ご回答ありがとうございます！初心者のような自分には理解するのにレベルの高いやり方ですが、関数の構造を理解するには助かります。図示までいただけて感謝です。

回答No.2

chonami

No.1さんの関数で、バッチリ変換できますが、複数の方法をご希望されてるので手作業の案で。

該当セル選択→データ→区切り位置→「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択
↓
次へ→スペースの位置に上向き矢印で区切られたデータが出てくるので、2・3・5本目をダブルクリックで削除
↓
次へ→データのプレビューで日付のセクションを選択して、上にある列のデータ形式の日付の中から「MDY」を選択
↓
完了

これで、曜日、日付、時間のセルに分かれます。
どこかのセルに日付と時間のセルを足し算し、表示形式を「yyyy/mm/dd hh:mm」
で如何でしょうか？

お礼

ご回答ありがとうございます。こちらも試してみました、こういうやり方もあるのかと勉強させていただきました！No.1さんのとも違う視点で同じ結果が出せるのはエクセルの面白いところだなあ、と初心者ながら感じています。

回答No.1

keithin

Ａ１に文字列があるとして
=IF(A1="","",(MID(A1,9,2)&" "&MID(A1,5,3)&" "&MID(A1,11,5))+TRIM(RIGHT(A1,8)))
セルの書式設定の表示形式のユーザー定義で年/月/日 時:分の表示を付けておきます。

お礼

早速のご返答ありがとうございます。MID関数とTRIM関数いうものを使えばよいのですね、まだまだ勉強中で非常に助かりました。

ちなみに例では秒数まで含んでおりませんが秒数まで含む場合(例：Fri Nov 2 2012 9:00:53 PM)ではどのように関数が変化するのでしょうか？自分なりにMID関数の文字数を変更してユーザー定義を「yyyy/m/d h:mm:ss」と変えてみたのですがうまく変換ができませんでした。都度のお願いとなり大変申し訳ありません・・・。