Date Convert Help

[graemestown]

Hi

I'm stuck. How would I convert this date (a SAP BW output) to a useable excel date format
21.10.2024

Many thanks!

 

[LegendaryAxe]

Try this:

Book5
	A	B
1	21.10.2024	10/21/2024
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)

 

[Cubist]

What is your regional date format?

 

[AhoyNC]

Another way.
Goto Data on ribbon

• Delimited
• Next
• Next
• Date DMY
• Finish

 

[Dave Patton]

N.B. We do not know the date format in your area. Format is shown in your system's Regional Settings.

Dates and Time 2024.xlsm
	A	B	C
4	21.10.2024		21-Oct-24
5	21.10.2024		21-Oct-24
6
2i

Cell Formulas
Range	Formula
C4	C4	=--TEXTJOIN("-",,INDEX(TEXTSPLIT(A4,"."),{1,2,3}))
C5	C5	=LET(a,TEXTSPLIT(A5,"."),DATE(CHOOSECOLS(a,3),CHOOSECOLS(a,2),CHOOSECOLS(a,1)))

 

[kvsrinivasamurthy]

If your regional settings for date is dd/mm/yyyy then
A1=21.10.2024
In B1

=SUBSTITUTE(A1,".","/")

 

[graemestown]

Hi...I have my regional format to dd/mm/yyyy.
The solution above works =--TEXTJOIN("-",,INDEX(TEXTSPLIT(A4,"."),{1,2,3}))
The others seem to still return the format in a 'text'

Thanks all for your input!

 

[Cubist]

Try:

=--SUBSTITUTE(A4,".","/")