エクセル：漢字混じりの日時文字列を時間データに変換

質問

日・時・分が漢字で入力されたエクセルの文字列データを、時間データに変換したいです。
マクロでも関数でも、どちらでもよいです。
どのようにすればよいでしょうか？

よろしくお願いします。

例）
--
2日8時間　→　56:00
2時間30分 →　2:30
5分 →　0:05
4時間　→　4:00

回答No.3

mt2008 · ベストアンサー

ユーザ関数で……
標準モジュールに下記のコードを入れ、適当なセルに =fTimestamp(A1)　の様に式を入れて下さい。
式を入れたセルの書式はユーザ書式で　[h]:mm としてください。

Function fTimestamp(sData As String) As Date
　　For i = 1 To Len(sData)
　　　　sOne = Mid(sData, i, 1)
　　　　If IsNumeric(sOne) Then
　　　　　　sNum = sNum & sOne

　　　　ElseIf sOne = "日" Then
　　　　　　fTimestamp = fTimestamp + Int(sNum)
　　　　　　sNum = ""

　　　　ElseIf sOne = "時" Then
　　　　　　fTimestamp = fTimestamp + Int(sNum) / 24
　　　　　　sNum = ""

　　　　ElseIf sOne = "分" Then
　　　　　　fTimestamp = fTimestamp + Int(sNum) / (24 * 60)
　　　　　　sNum = ""
　　　　End If
　　Next i
End Function

お礼

図入りでわかりやすい回答を、ありがとうございます。
試してみたところ、最初はユーザ関数が認識されなかったのですが（文字列として表示されてました）、何度か試したらうまく計算してくれるようになりました。
（何がいけなかったのかは、不明です。）
とにかく、バッチリ手軽に計算してもらえるようになって、非常に快適です！

ベストアンサーに選ばせていただきます。
ありがとうございました。

回答No.5

bunjii

回答No.4の別解の一部に誤りがありました。
I2=IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE($A2,$G2&$G$1&$H2&$H$1,""),I$1,REPT(" ",100)),100)*1,"")
を次のように訂正します。
I2=IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE($A2,$G2&IF(G2="","",G$1)&$H2&$H$1,""),I$1,REPT(" ",100)),100)*1,"")

画像も訂正後のものを貼付します。

お礼

試してみたところ、C列に計算する方は、うまくいきました。
別解の方は、なぜかどの列も空欄になってしまい、計算できませんでした。
でも、１列で計算できた方がよいので、C列に計算する方法の方がよいです。

望みの結果を得ることができました。
ありがとうございました。

回答No.4

bunjii

>日・時・分が漢字で入力されたエクセルの文字列データを、時間データに変換したいです。
関数では文字列から数字を切り出して日時のシリアル値に変換します。
A列に元データの文字列があり、C列に日時の値にして表示形式で時間数の値にします。
C2=SUM(IFERROR(LEFT(A2,FIND("日",A2)-1),0),IFERROR(MID(A2,IFERROR(FIND("日",A2),0)+1,FIND("時間",A2)-IFERROR(FIND("日",A2),0)-1),0)/24,IFERROR(MID(A2,IFERROR(FIND("時間",A2)+2,1),FIND("分",A2)-IFERROR(FIND("時間",A2)+2,1)),0)/1440)
此処で、SUM関数の必要はありませんが日、時、分の値をSUM関数の中で確認しながら数式を組み立てたためそのまま残しました。
別解として日、時、分の数値をG、H、I列に切り出してからE列へ解を得る方法を示します。
G2=IFERROR(LEFT(SUBSTITUTE($A2,G$1,REPT(" ",100)),100)*1,"")
H2=IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE($A2,G2&G$1,""),H$1,REPT(" ",100)),100)*1,"")
I2=IFERROR(LEFT(SUBSTITUTE(SUBSTITUTE($A2,$G2&$G$1&$H2&$H$1,""),I$1,REPT(" ",100)),100)*1,"")
E2=SUM(IFERROR(G2*1,0),IFERROR(H2*1,0)/24,IFERROR(I2*1,0)/1440)
これらの数式ではIFERROR関数を使っていますのでExcel 2007以降のバージョンが対象です。
尚、数値の表示形式は [h]:mm です。

お礼

回答No.5へのお礼をご参照ください。

回答No.2

eden3616

No1で修正です。

(4)でD1に張り付けてオートフィルする数式を以下のようにしてください。
=INT(C1/3600) & ":" & TEXT(MOD(C1,3600)/60,"00")

お礼

ありがとうございます。
試してみましたところ、望みどおりの結果を得ることができました。

ただ、コピー＆値貼り付けや置換が必要なのが、ちょっと面倒ですね。
一度式を入れたら、自動的に計算してくれるものが欲しいので、引き続き回答を募集します。

回答No.1

eden3616

急がば回れ？

A列に以下のように例題のようにあるとします（○｜は行番号）
１｜2日8時間
２｜2時間30分
３｜5分
４｜4時間

(1)以下の数式を「B1」に貼付て、A列の行数分オートフィル
="イコール" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"日","*24*60*60+"),"時間","*60*60+"),"分","*60")&"E","+E",""),"E","")

　　B列の結果＿＿＿＿＿＿＿＿＿
　　１｜イコール2*24*60*60+8*60*60
　　２｜イコール2*60*60+30*60
　　３｜イコール5*60
　　４｜イコール4*60*60

(2)B列をコピーしてC列に値として貼付

(3)C列を選択してCtrl+Fで検索窓を表示し、置換タブへ切り替えて
　　「イコール」を「=」へ全て置換

　　C列の結果＿＿＿＿＿＿＿＿＿
　　１｜201600
　　２｜9000
　　３｜300
　　４｜14400

(4)以下の数式を「D1」に貼付て、A列の行数分オートフィル
=INT(C1/3600) & ":" & MOD(C1,3600)/60

　　D列の結果＿＿＿＿＿＿＿＿＿
　　１｜56:0
　　２｜2:30
　　３｜0:5
　　４｜4:0

お礼

回答No.2へのお礼をご参照ください。