excelvbaexcel-formulaformatting

Date format issue when I try to find the first occurrence of names with associated dates


I am working with two columns in Excel:

Column A: Contains names (e.g., Giovanni, Maria, Leo, etc.).

Column B: Contains dates (e.g., 01/2025, 06/2023, etc.), in the format DD.MM.YYYY (e.g., 29.10.2023).

I want to find the first time a name appears in Column A and retrieve the earliest date associated with that name from Column B. For that in Column C I used:

=TEXT(MINIFS(B:B;A:A;A2); "DD.MM.YYYY")

As result:

enter image description here

As you can see, there were issues with how Excel was handling the date format.

The date was displaying incorrectly, showing 23.01.YYYY instead of the actual year (e.g., 2021).

I am know due to formatting or Excel not recognizing the date properly.

What I have done so far was ensure the date format in Excel, but it didn't work.

Any suggestions?


Solution

  • From Shrotter's comment.

    My lenguage setting is norwegian. I had to change the formula to:

    =TEXT(MINIFS(B:B;A:A;A2); "DD.MM.ÅÅÅÅ")