Cell showing date.xlsx wanting to pull the date without xlsx to another cell in date format

[Jaffabfc]

Hi,

I have a lot of cells which has dates.xlsx in the columns for example 030124.xlsx in cell N2
i am wanting in cell O2 to say 03/01/24.

I have the formula in cell O2 saying =left(n2,6) which brings the date but then i cant format it to the date.
I have tried also in P2 putting =DATE(RIGHT(O2,2),MID(O2,3,2),LEFT(O2,2))
But sitll not working also tried =DATE(LEFT(O2,2),MID(O2,3,2),RIGHT(O2,2)) but that doesnt work how i want it either.

Any ideas?

thanks

 

[Georgiboy]

Here is one option, I am sure more will follow (probably shorter):

Book1
	N	O	P
1		Result 1	Result 2
2	030124.xlsx	03/01/2024	03/01/2024
3	040124.xlsx	04/01/2024	04/01/2024
4	050124.xlsx	05/01/2024	05/01/2024
Sheet1

Cell Formulas
Range	Formula
P2:P4	P2	=BYROW(N2:N4,LAMBDA(x,LET(s,MID(x,SEQUENCE(,LEN(x)/2,,2),2),--TEXTJOIN("/",,TAKE(s,,3)))))
O2:O4	O2	=LET(s,MID(N2,SEQUENCE(,LEN(N2)/2,,2),2),--TEXTJOIN("/",,TAKE(s,,3)))
Dynamic array formulas.

 

[Jaffabfc]

brilliant thanks, not heard of either of those formulas so will look at learning those ones

 

[RoryA]

Assuming that date format matches your regional settings, something like:

=TEXT(LEFT(N2,6)+0,"00\/00\/00")+0

should work (format the cell as date).