Calc: If-Statement pulling data from multiple cells - date is not shown correctly


many thanks for the support provided here by everyone!

My issue:
I want to write names and birth dates of people in a sheet. I am using a dropdown list (Data->Validity) in cell A1 to indicate how many people are about to be entered. A2 to An then read “Person 1, Person 2, … Person n”.
B2 to Bn contain their names.
C2 to Cn their birth date.

In D1 I want to create a letterhead. The letterhead should contain each persons name and birthdate, and the wording should be according to the number of people (for one person, Concering “name”, born on “birth date”; for multiple people “Corning the people …”). For this I use an IF-statement:

=IF(A1=1;“Concerning “&B2&”, born”&C2)&IF(A1=2;“Concering the persons “&B2&”, born”&C2&" and"&B3&", born"&C3","")

The IF-statement works, the problem is, that the date is not displayed correctly. LO outputs the number it uses internally (I learned that LO stores dates internally as numbers, as is explained here) instead of a birth date. C2 to Cn are formatted correctly as date. Formatting D1 as date didn’t do the trick, none of the formatting options for D1 make the date display correctly.

Any help is appreciated, thank you!

The & operator works with the cell content, not its formatted display string, and as dates are formatted date serial numbers that’s what it takes. Instead of C2 use TEXT(C2;"YYYY-MM-DD")

Working, thank you!

Use TEXT spreadsheet function to format date. See Number Format Codes for details.

Thank you!