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]

I am a bit unclear.

You mentioned this:

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

but the data in your image look very different.

Can you just show us:
- what the data you have looks like exactly
- what exactly you are trying to get out of that
I think you may have a lot of extra unneeded information in this post confusing matters (unless I really do not understand what you are asking).

Also, which version of Excel are you doing this on? You have BOTH 2016 and 365 listed in your profile.
365 has some really powerful new text functions that might be helpful here that 2016 does not have.

 

[Arts]

Apologies Joe

To summarise what I have is the statement Generated on behalf of Tom Pitt on Tuesday, January 9, 2024 9:02:31 AM

What I am after from the above is to extract the date and show as 09/01/24

 

[Joe4]

You didn't answer my question about whether you are doing this in 2016 or 365.

Here is an answer for 365.

If you want the answer to be a valid date, use this formula:

=DATEVALUE(TRIM(TEXTAFTER(A4,",")))

and apply this custom format to the cell "dd/mm/yy".

If you want a text representation of that value, you can use:

=TEXT(DATEVALUE(TRIM(TEXTAFTER(A4,","))),"dd/mm/yy")

 

[Arts]

Sorry (once again) I am using 365. Failed at multi tasking (Teams call and replying to this)

I'll give those a whirl in a mo, seems so simple after seeing your responses!!

Thanks Joe

 

[Arts]

Going by my form on this post I'm sure I'm more than likely doing something wrong but this seems to give me #VALUE! error (on both formula's)

 

[Joe4]

I wonder if it might be a regional setting thing, and you are using different Regional Settings for Dates than I am.

What does this return?

=TEXTAFTER(A4,",")

Does that part work without error?

 

[Arts]

That seems to work

 

[Joe4]

What does this formula return for you?

=DATEVALUE("January 9, 2024")

If that doesn't work, that confirms that regional settings are affecting the way my formula works on your computer.

 

[Arts]

DATEVALUE("January 9, 2024") throws up a VALUE#

With my very limited skills it seems to fall apart when the DATEVALUE is added as this =TRIM(TEXTAFTER(A4,",")) seems to be fine.

Is there a setting I can edit on my end within Excel or would this need the intervention of IT?