how to convert SAP date into true date

[fayez_MrExcel]

I have downloaded SAP records with a date as (e.g) 31.08.2021, 11.02.2021 and the excel does not recognize it as date, how can i convert it to true date. Thanks

 

[Dave Patton]

N.B. My regional settings are dd-mm-yy

The double negative coerces the text to a number (the date)
Format to your preference.

Date and Time.xlsm
	A	B
10	31.08.2021	31-Aug-21
11	11.02.2021	11-Feb-21
12
12c

Cell Formulas
Range	Formula
B10:B11	B10	=--SUBSTITUTE(A10,".","-")

 

[fayez_MrExcel]

N.B. My regional settings are dd-mm-yy

The double negative coerces the text to a number (the date)
Format to your preference.

Date and Time.xlsm
	A	B
10	31.08.2021	31-Aug-21
11	11.02.2021	11-Feb-21
12
12c

Cell Formulas
Range	Formula
B10:B11	B10	=--SUBSTITUTE(A10,".","-")

Thanks that works great.... it saves me a lot of time