Date written need to extract into date format

[Arts]

Hi all

I have the below text which is generated from a report (the length of the name can vary as this can be run be a few different people)

Generated on behalf of Tom Pitt on Tuesday, January 9, 2024 9:02:31 AM

What I am hoping to do is to extract the date and get it into a format (ie 01/02/24) so I can apply a formula to let the individual know how long it has been since the report has been run. I've started backwards in that I have the formula for this part but the extraction of the date is proving difficult for me. (image attached showing desired outcome once date can be extracted from the string)

My efforts so far have I have managed to get extract "January 9 2024 9:02:31 AM" by using =TRIM(SUBSTITUTE(MID(A4,SEARCH(",",A4),40),",","")) but I think I am going down the wrong path here....

Any help as always is most appreciated.

 

[Joe4]

OK, it sounds like the issue is that with your Regional Settings, your computer does not recognize "January 9, 2024" as a valid date!
Can you follow these steps here (Change the Windows regional settings to modify the appearance of some data types in Access databases. - Microsoft Support), and find your Regional Date settings, and then copy/paste the screen here for us to see?

It should look something like this:

 

[Arts]

Hopefully this is right screen

 

[FormR]

Hi, here's a less regionally specific option you can try:

=LET(PARTS,TEXTSPLIT(TEXTAFTER(A4,","),{" ",","},,1),DATE(INDEX(PARTS,3),MATCH(INDEX(PARTS,1),TEXT(DATE(1,SEQUENCE(12),1),"mmmm"),0),INDEX(PARTS,2)))

 

[Joe4]

Try this:

=LET(x,TEXTBEFORE(TRIM(TEXTAFTER(A4,","))," ",3),DATEVALUE(TEXTBEFORE(TEXTAFTER(x," "),",")&" "&TEXTBEFORE(x," ") & " " & RIGHT(x,4)))

 

[Arts]

Both of those do the trick! If it's ok with you FormR I'll mark Joe's response as the solution as the time he spent helping me on this.

Thank you both and once again thanks Joe for all the help on this.

 

[Joe4]

You are welcome.
Glad we were able to help!