Excel Date separate with DAY MONTH and YEAR

Question

I am having trouble with the functions =DAY(), =MONTH(), and =YEAR, even though it is a fairly simple question (). It fails and gives me an error. I reject CSV.

Excel Sheet:

A1 19.03.2023
A2 =DAY(A1)

but A2 gives #VALUE error. The Format is in General for A1 and A2 even if I set to Date nothing changes.

Answer 1

If it must be done inside of Excel, one approach is to format the data using SPLIT and CONCAT. If necessary, the CONCAT might even be rearranged.

TEXTSPLIT(A1,".")

CONCAT(B2,"/",C2,"/",D2)

Excel

Another approach, and the one I use 99/100, is to import the data into Power Query, perform any necessary transformations, and then export the cleaned-up and ready-to-use data to another page (or even another workbook).

Assuming that you actually have a header "Date" in A1, and the data in question was in A2:

1. Ctrl+T to turn it into a Table
2. From the Data tab, "Get Data from Table/Range"
3. From within Power Query, right-click the Date column, select "Replace values".
4. Value to Find "."
5. Replace with (leave blank)
6. Results: 19032023

Power Query 1