=TEXT Formula for 2022

This:

=LEFT(K2,2)&"/"&MID(K2,4,2)&"/"&MID(K2,7,2)&MID(K2,FIND(" ",K2,1),6)

Try:

Formula:

Please Login or Register  to view this content.

Note that using LEFT and MID gives you Text output, not a numeric date and time value.

You're welcome. Thanks for the rep.

I would control the format of your date within the cells. Knowing the configuration of the data source and provided it adheres to this strict date time formatting you can tell the DATE formula where to find the YEAR, MONTH, DAY within your data. I add 2000 to your year so Excel doesnt bump you back to the 1900's.

COLUMN C
=TEXT(DATE(2000+RIGHT(LEFT(K2,8),2),RIGHT(LEFT(K2,5),2),LEFT(2))+TIMEVALUE(RIGHT(K2,6)),"DD/MM/YY HH:MM")

COLUMN D
=TEXT(DATE(2000+RIGHT(LEFT(K2,8),2),RIGHT(LEFT(K2,5),2),LEFT(2)),"mmm")

COLUMN E
=WEEKNUM(DATE(2000+RIGHT(LEFT(K2,8),2),RIGHT(LEFT(K2,5),2),LEFT(2)),21)

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.