Extract date from text string in Excel

[jameson222]

Hi,
I have a problem where i try to extract dates from a string of texts in Excel. The problem i have is that the dates have slightly different formats.
Example 1: MM/DD/YYYY (10/29/2019)
Example 2: M/D/YYYY (9/1/2019)

Below are examples of the text strings from which i need to extract and convert this to a proper Excel Date:
9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity
9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity

One helpful note is that the date always come after the letters "QT" (marked bold above).
Also, date appear in the string twice, i only need to extract the first date.

Can you advise on a formula that can manage to extract both formats? Thanks!!

 

[Peter_SSs]

Here is another way to try. Format the formula column with whatever date format you want.
This is based on a fixed position in the string for "QT"

20 03 15.xlsm
	A	B
1	9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity	29-October-2019
2	9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity	1-September-2019
Extract Date

Cell Formulas
Range	Formula
B1:B2	B1	=DATE(MID(A1,FIND(" ",A1,21)-4,4),SUBSTITUTE(MID(A1,21,2),"/",""),SUBSTITUTE(MID(A1,FIND("/",A1,22)+1,2),"/",""))

 

[Toadstool]

This worked perfect to do the correct extraction!
Perhaps basic question, but this is still recognized in the new cell as text. How do i best convert this to a date format? When i try =Datevalue function it doesnt work?

DATEVALUE should work but you'll need to format the cells in your preferred date format. Excel holds dates as days since Jan 1st 1900 (or 1904 for Mac) so DATEVALUE should give you 43767 and 43709 as in column D of this example. Column B has the date formatted as dd-mmm-yy as my preferred date format:

Book1
	A	B	C	D
1	9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity	29-Oct-19		43767
2	9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity	01-Sep-19		43709
Sheet1 (2)

Cell Formulas
Range	Formula
B1:B2	B1	=DATEVALUE(MID(A1,SEARCH("QT",A1)+2,SEARCH(" ",A1,SEARCH("QT",A1))-SEARCH("QT",A1)-2))
D1:D2	D1	=DATEVALUE(MID(A1,SEARCH("QT",A1)+2,SEARCH(" ",A1,SEARCH("QT",A1))-SEARCH("QT",A1)-2))

 

[jameson222]

Hey Everyone!
Thanks for all the replies and help so far! And apologies for the late replies, i been out and about all day! I prepared a sample file that i was hoping to upload, but as im new to this forum i just realized that doesnt seem to be possible here. So i try to put notes below:

Peter_SSs: The attached image should show what happens with your suggestion. i tried to format the cell as General, Date and Text with same result. If you see something wrong please tell. Standard Date system is: YYYY-MM-DD. And yes, number of characters up to _QT is always the same as its computer names so they dont change.

- What does "is number" give for toadstools formula? - it gives a "false" (shown in A4). but with and without +0

Format of my cells is set to: Date -> YYYY-MM-DD

Any more tips or advise is greatly appreciated!! Thanks a bunch!

 

[MARK858]

For your Standard Date system we don't want to know what the format of your cells is, we want to know what is your Windows regional short date setting.

 

[jameson222]

Hey Everyone!
Thanks for all the replies and help so far! And apologies for the late replies, i been out and about all day! I prepared a sample file that i was hoping to upload, but as im new to this forum i just realized that doesnt seem to be possible here. So i try to put notes below:

Peter_SSs: The attached image should show what happens with your suggestion. i tried to format the cell as General, Date and Text with same result. If you see something wrong please tell. Standard Date system is: YYYY-MM-DD. And yes, number of characters up to _QT is always the same as its computer names so they dont change.

- What does "is number" give for toadstools formula? - it gives a "false" (shown in A4). but with and without +0

Format of my cells is set to: Date -> YYYY-MM-DD

Any more tips or advise is greatly appreciated!! Thanks a bunch!

Note! The cell references in first screenshot shows wrong in the "D" column, i cleaned away some columns and forgot to change, this screenshot is corrected!

 

[jameson222]

Regional short date should be as i noted it?

 

[MARK858]

I take it that it has been manually amended at some time as that is the International format.
We will try another way...
In a cell put the date 03/06/2020 and format the cell as General. What number does it give you?

 

[jameson222]

Its getting late here but now i got a few more things to try first thing in the morning!

this was my first post in this forum and i honestly didnt know if replies would take minutes / hours or days. So this is amazing! i will do my best to be off help here to going forward!

ill post as soon as i tried all suggesions!

 

[MARK858]

If when you enter 03/06/2020 and format as General you get 43985 or 45447 then try post number 11 by Peter_SSs.

 

[lrobbo314]

Another option using regular expressions in a UDF.

Book1
	A	B
1	9CVD0001FECDS_TG1_QT10/29/2019 11:57 PM10/29/2019 11:57 PMFailed Activity	10/29/2019 11:57 PM
2	9CVD0029FECDS_TG1_QT9/1/2019 12:43 AM9/1/2019 1:35 AMMissed Activity	9/1/2019 12:43 AM
Sheet3

Cell Formulas
Range	Formula
B1:B2	B1	=getDate(A1)

Function getDate(s As String)
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "QT(\d{1,2}\/\d{1,2}\/\d{4}\s\d{1,2}:\d{2}\s(AM|PM))"
    getDate = .Execute(s)(0).submatches(0)
End With
End Function