エクセルのデータから日付を抜き出す方法について

質問

エクセルで日時を含む値のセルから2ヶ月以内の日付があれば気づけるような仕組みを作ろうとしています。

データの内容は以下のような形です。

aaaaa
bbbbb
end : 12:59:59 25 Feb japan 2019
ccccc
ddddd
end : 18:33:42 31 Mar japan 2019
・
・
・
上記のように一つの列にデータが複数あり、3行目、5行目のような形式で日時が記載されたセルがあります。
aaaaa、bbbbbは日時とは全く関係のないデータです。

LEFTやMID関数で日時を抜き出し、現在の日時より62日以内の数値だけわかるように表示させる。
というようなことを考えましたが、抜き出したデータが日付として認識されなかったりとうまくいきません。

やり方は問いませんので、どなたか知恵をお貸しいただけると幸いです。

回答No.1

msMike · ベストアンサー

添付図参照
B1: =IF(LEFT(A1,3)="end",IF((MID(A1,16,7)&RIGHT(A1,4))*1<=TODAY()+62,(MID(A1,16,7)&RIGHT(A1,4))*1,""),"")

お礼

ありがとうございます。
msMikeの情報を基本に、他の方の案と合わせて
希望していた結果が得られました。
早急なご回答ありがとうございました。

回答No.6

bunjii

回答No.4の数式に誤りがありました。
ABS(DATEVALUE(SUBSTITUTE(MID(A2,7,LEN(A7)),"japan",""))-TODAY())<62
　　　　　　↓
ABS(DATEVALUE(SUBSTITUTE(MID(A2,7,LEN(A2)),"japan",""))-TODAY())<62
この数式を使って条件付き書式でセルの塗り潰しをした結果は添付画像のようになります。
数式は先頭に"="を付加して次のようになります。
A2:A8を選択して数式を下記のようにします。
=ABS(DATEVALUE(SUBSTITUTE(MID(A2,7,LEN(A2)),"japan",""))-TODAY())<62
塗りつぶしの色を薄い緑にしてみました。

お礼

ありがとうございます。
他の方の案と合わせて希望の結果を得ることができました。
詳細なアドバイスありがとうございました。

回答No.5

imogasi

関数を希望なのだろうが、VBAで初歩的手法でやってみた。
例データ　A2:B１３
aaaaa
bbbbb
end : 12:59:59 25 Feb japan 2019 2019/2/25
ccccc
ddddd
end : 18:33:42 31 Mar japan 2019 2019/3/31
eeeee
fffff
end : 12:59:59 28 Feb japan 2019 2019/2/28
ggggg
hhhh
end : 12:59:59 28 mar japan 2019 2019/3/28
ーー
標準モジュールに
Sub Sample4()
lr = Range("A100000").End(xlUp).Row
For i = 4 To lr Step 3
If Mid(Cells(i, "A"), 1, 3) = "end" Then
x = Replace(Cells(i, "A"), "japan", "") 'japanを削除
x = Replace(x, "end : ", "") 'end :を削除
Cells(i, "B") = DateValue(x) 'B列に日付をセット
'---
dt = DateAdd("m", 2, Date) '２か月先日付
If Cells(i, "B") < dt Then '２か月先より小であれば
Cells(i - 2, "A").Resize(3, 3).Interior.ColorIndex = 6　'黄色
End If
End If
Next i
End Sub
ーー
２，３，４行
８、９、１０行
11,12,13行に色が着く。
＝＝
データ依存性の大きい、手抜きプログラムです。
第２行目から、正確に「３行目ごとに日付データがある」とか、のルールが実際データでは、崩れると、プログラムの修正が必要です。
ーー
また、
「end :」の全角半角、前後にスペースの有無 、なども予想と違う（違うものが混じっている）場合は、プログラムの修正が必要です。

お礼

ありがとうございます。
今回は使用する方がVBAはわからないので関数で結果を取得するようにいたしました。
VBAも大変参考になりました。

回答No.4

bunjii

回答No.2への補足ありがとうございます。
日付を含む文字列の先頭に必ず"end : "があると言う条件ではIF関数でチェックの要否を仕分けした方が良いでしょう。
=IF(LEFT(A2,6)="end : ","対象外",IF([今日の日付と比較],[62日未満],[62日以上]))
A列の日付情報の抽出は下記の数式で良いでしょう。
DATEVALUE(SUBSTITUTE(MID(A2,7,LEN(A2)),"japan ",""))
比較対象の日付が今日以降の場合と今日以前の場合がある場合は[今日の日付と比較]の数式を次のようにすると良いでしょう。
ABS(DATEVALUE(SUBSTITUTE(MID(A2,7,LEN(A7)),"japan",""))-TODAY())<62
[62日未満]と[62日以上]の処理の具体的な提示が無いので最終的な数式はご自身で組み立ててください。

回答No.3

SI299792

DATEVALUE を使えば、文字列を日付に変換できます。
A3にデータがある場合
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A3,"end : ",""),"japan ",""))
で日付に変換できます。

62日以内であれば
=IFERROR(IF(TODAY()+62>=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A3,"end : ",""),"japan ","")),"○",""),"")
のような形にするといいでしょう。

お礼

ありがとうございます。
DATEVALUEを使用して、他の方の案と合わせて希望の結果を得ることができました。
大変参考になりました。

回答No.2

bunjii

>上記のように一つの列にデータが複数あり、3行目、5行目のような形式で日時が記載されたセルがあります。
Excelのバージョンは2007以降でしょうか？
処理対象の列記号を提示してください。
また、日時の情報に"japan"が含まれていますが他国の場合もありますか？
時刻の書式は"hh:mm:dd"でしょうか？
更に、日時が含まれるセルのデータは先頭のend : が必ずありますか？
これらの条件の提示が無いと的確な回答が困難です。

補足

ありがとうございます。
以下、補足いたします。

エクセルのバージョンは2007以降です。
国は必ずjapanがはいり、時刻はhh:mm:ss、先頭のend : も必ず入ります。
列記号はAの列でお願いします。

情報に不足している点ありましたら、ご指摘下さい。
よろしくお願いいたします。