Excel Not Changing Serial Date to Formatted Date

How do I fix the serial date to a formatted date after I've tried the traditional formatting options? I tried changing via the format cells using the date; I tried a custom date, and still it won't change. I changed to text and back to general, then a formatted date. Nothing works.

Here is the formula: =TEXTJOIN(";",TRUE,XLOOKUP($B2&$C2&G$1,CompletedTraing!$C$2:$C$400&Complete dTraing!$D$2:$D$400&CompletedTraing!$K$2:$K$400,CompletedTraing!$M$2:$M$400 ,"NC"))
In column F on the green table, the date is formatted correctly, there is no formula here.

I used this formula on another spreadsheet and the dates are formatted as dates (see blue table)

Attached screenshot (green) this is the problem spreadsheet.
Attached screenshot (blue) this is the same formula used on another spreadsheet and the dates are formatted as dates.



Attachment 31024

Attachment 31025

You say you formatted the cells as date? Might provide your workbook for analysis. Follow instructions at bottom of my post.

Thank you. The spreadsheet is connected to Access, then I have query connection. I tried copy and paste as values to break the connections so I can send to you. I noticed the cell message shows the number stored as text. When I select convert to number this allowed me to format to a date.

You can't see the true spreadsheet if I paste as value.

OHHH Wait! My original spreadsheet (blue table in my original message), this is not connected to Access nor a query. Something is going wonky with my connections. :bug:

I removed the "textjoin" from the formula. Now it will allow me to change the serial date to a formatted date.

What's odd, in my original spreadsheet I used the textjoin and it allowed the date to be formatted as a date.

I also can't recall why I used the textjoin with the xlookup. I did not need the textjoin.

Textjoin returns text, which you cannot format with number formats (other than prepending/appending other text).