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.

 

[myall_blues]

What produces the report? Is it output from a macro, or are you just using the standard Excel printing to print a page?

 

[mIkeChchNZ]

The report is run through a software package we use at work and is exported as an excel sheet.

 

[myall_blues]

So the date is in a specific cell?

 

[mIkeChchNZ]

Yes. It is all in one cell.

 

[Cubist]

Format only works on numerical data. Is your data numerical data or is it text? Check with =ISNUMBER().

 

[mIkeChchNZ]

Says false so would this mean I need to separate each component and convert it somehow?

 

[myall_blues]

Yes. Here's a formula (assumes date in A1) but that would need to go in another cell. You could set the text colour of the original to white to hide it.

=TEXT(LET(ldate,TRIM(TEXTSPLIT(A1,",")),DATE(LEFT(CHOOSECOLS(ldate,3),4),MONTH(1&TEXTBEFORE(CHOOSECOLS(ldate,2)," ",1)),TEXTAFTER(CHOOSECOLS(ldate,2)," ",1)))+LET(ftime,TEXTAFTER(CHOOSECOLS(TRIM(TEXTSPLIT(A1,",")),3)," "),htime,TEXTBEFORE(ftime,":"),TIME(IF(TEXTAFTER(ftime," ")="p.m",VALUE(TEXTBEFORE(ftime,":"))+12,TEXTBEFORE(ftime,":")),MID(ftime,4,2),MID(ftime,7,2))),"d/mm/yyyy, h:mm")

@Cubist will come up with a better one

A VBA solution could change the original cell value so if you prefer that let us know.

 

[Cubist]

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,".","")," ")

 

[mIkeChchNZ]

Thanks, that's one hell of a formula. Tried it and it works but doesn't show in 24hr format. Would I just need to change h:mm to [h]:mm?