Date & time format conversion

[Median]

Hi all,

I'm trying to find an efficient way of converting an ugly date & time format.

The raw data arrives as: "Tue, Dec 28, 2021, 11:36 AM (7 days ago)" & I'm trying to convert it into: "28/12/2021" & "11:00" (sheet included below, with multiple examples)

Annoyingly =Month =Day =Hour etc don't seem to like the data

Thanks
Mdn

[6StringJazzer]

These two formulas shown in column D and E below will do it.

Values as displayed

	A	B	C	D	E
1	Date & Time	Date	Time (hour)
2	Tue, Dec 28, 2021, 11:36 AM (7 days ago)	12/28/2021	11:00	12/28/2021	11:00

Underlying formulas

	A	B	C	D	E
1	Date & Time	Date	Time (hour)
2	Tue, Dec 28, 2021, 11:36 AM (7 days ago)	44558	0.458333333333333	=DATEVALUE(MID(A2,6,FIND(",",A2,13)-6))	=TIME(HOUR(TIMEVALUE(MID(A2,FIND(",",A2,13)+2,8))),0,0)

[Median]

Cheers Jeff

[6StringJazzer]

I should have mentioned this but I guess you sorted it. The dates you see are U.S. because that's just the way they are displayed for me. The same formula will give you d/m/yyyy format.