Change date format

[mIkeChchNZ]

I have a report that returns the date and time as Friday, July 12, 2024 07:08:00 p.m.

Is there a formula or macro I can use to change this to display as d/mm/yyy h:mm

Friday, July 12, 2024 07:08:00 p.m.

 

[mIkeChchNZ]

Try this. After inserting the formula, you'll see numbers. That's just how Excel stores dates, but you can format cell -> custom.

Book1
	A	B
1	Text	d/mm/yyy h:mm
2	Friday, July 12, 2024 07:08:00 p.m.	12/07/2024 19:08
Sheet2

Cell Formulas
Range	Formula
B2	B2	=--TEXTAFTER(SUBSTITUTE(A2,".","")," ")

I get a #VALUE error

 

[Cubist]

I get a #VALUE error

There are probably some rouge spaces. What if you add TRIM?

=--TRIM(TEXTAFTER(SUBSTITUTE(A2,".","")," "))

 

[Cubist]

*Should TRIM first*

=--TEXTAFTER(SUBSTITUTE(TRIM(A2),".","")," ")

 

[Alex Blakenburg]

I believe Cubist default date is US and I am wondering if that it why it doesn't work for you. My default is d/mm/yyyy and it doesn't work for me either.
Does this work:

=--TEXTJOIN(" ",TRUE,CHOOSECOLS(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,",",""),".","")," "),3,2,4,5,6))

 

[mIkeChchNZ]

I believe Cubist default date is US and I am wondering if that it why it doesn't work for you. My default is d/mm/yyyy and it doesn't work for me either.
Does this work:

=--TEXTJOIN(" ",TRUE,CHOOSECOLS(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,",",""),".","")," "),3,2,4,5,6))

Looks like it's working. Really appreciate everyone's help!

 

[Alex Blakenburg]

Thanks for letting us know. It was definitely a team effort.

 

[Cubist]

My default is d/mm/yyyy and it doesn't work for me either.

I would think that using the full month name, it'd picked it up universally e.g. July. Now, I know the order does matter.

 

[Alex Blakenburg]

I would think that using the full month name, it'd picked it up universally e.g. July. Now, I know the order does matter.

Its harder for you to pick up since the default is generally US format so it will work for you, and give you no reason to think it won't work for others.