Text into Date in Google Sheets

Question

When importing data from Magento to Google Sheets, the date of each transaction is in text format.

For example:

1.1.2020 14.54.16

31.12.2019 16.17.32

How can I format the date part into value? The time of the day should be eliminated.

Desired output:

43830

43831

Test sheet here: https://docs.google.com/spreadsheets/d/1IyGzzrWQMeM-FbV3TVBl23V0NTKq7aWfuf88DKnQwwQ/edit?usp=sharing

Answer 1

1) The Time component can be extracted using REGEXTRACT function, as I have placed it in the test sheet

=REGEXEXTRACT(A2, "(\d{1,2}\.\d{1,2}.\d{4,4})")

2)replace . with *

=SUBSTITUTE(B2,".","*")

3)Split on *

=SPLIT(C2,"*")

4)Create Date Object

=DATE(F2, E2, D2)

5)Convert to Numerical

=DATEVALUE(G2)