Date Extraction Issues

[mikestier]

Good afternoon all,

I am having issues extracting a date from a specific text thread. Any assistance would be appreciated.

Backstory: The text comes from a CSV file I have downloaded.

Thread: "Wed Apr 29 2020 18:41:18 GMT-0500 (Central Daylight Time)"

Goal: What I need is to pull the date only [Apr 29 2020" from the thread, convert it to a proper date format to use with COUNTIFS, SUMIFS, and VLOOKUP.

Attempts:
1. I tried a "=MID()" function to pull the specific text, then format to date via the format cell option. Did not work.
2. I tried a "=MID()" function to pull the specific text, then format to date via the "=DATE()" and "=DATEVALUE()". Did not work.
3. I tried a "=MID()" function to pull the specific text [three times, once for the Month, Day and Year and placed those into their own cells, then format to date via =IFERROR(DATE(YEAR(C2),MONTH(A2),DAY(B2)),""). Did not work.

Any insight would be greatly appreciate!
Mike

[Fluff13]

Hi & welcome to the board.
How about
=DATE(MID(A2,12,4),MONTH(MID(A2,5,3)&1),MID(A2,9,2))
This assumes that the day will always be 2 digits.

[Bo_Ry]

Please try
=--(MID(A2,9,2)&REPLACE(MID(A2,5,11),5,2,))

[Limor_OP]

try this maybe:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),2*LEN(A1)+1,LEN(A1)))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),1*LEN(A1)+1,LEN(A1)))&" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),3*LEN(A1)+1,LEN(A1)))

[mikestier]

That worked exactly as I needed! Thank you!

-M

[Limor_OP]

That worked exactly as I needed! Thank you!

-M

which one?

[Fluff13]

No sure who your talking to, but glad to help & thanks for the feedback.

[Limor_OP]

Hi & welcome to the board.
How about
=DATE(MID(A2,12,4),MONTH(MID(A2,5,3)&1),MID(A2,9,2))
This assumes that the day will always be 2 digits.

Fluff13 - why does this formula also returns error in my file? is it also because of the office version? Maybe it is time for me to upgarde....

[mikestier]

Fluff13's "=DATE(MID(A2,12,4),MONTH(MID(A2,5,3)&1),MID(A2,9,2))"

[Fluff13]

No idea why you are getting an error, unless it's down to language settings. None of those functions are new.
Also your formula returns a text value, rather than a date.

[Limor_OP]

Can you please check it in the file?

Thanks!

[Fluff13]

You've got the " at the start of the string, which puts the formula out.

[Limor_OP]

No idea why you are getting an error, unless it's down to language settings. None of those functions are new.
Also your formula returns a text value, rather than a date.

You're right.....

[Limor_OP]

removed the " , Still doesnt work...... :/

[Fluff13]

In that case may be language.