Show readable date in concatenate

[opislak]

Hi all,

How do you show a date in a readable format in a cell where two fields are concatenated.
Example:
- in cell A1 is a project name --> TestProject
- in cell B1 is a timestamped date --> 20-05-2021 15:35:01
- in cell C1 I want to concatenate both --> =A1&" ("&B1&")"
But the result is unreadable. It shows TestProject (44336,6493171296). I would like it to show me TestProject (20-05-2021 15:35:01)

Thanks for any help!!
Pat.

 

[RoryA]

You need to add the TEXT function - eg

=A1&" ("&TEXT(B1,"dd-mm-yyyy hh:mm:ss")&")"

 

[Fluff]

How about

=A1&TEXT(B1,"(dd/mm/yyyy hh:mm:ss)")

 

[opislak]

Thanks both. That works. Except for the fact that I had to translate "yyyy" in "jjjj" and "hh" into "uu" (dutch). I don't understand, for my Excel is in English (I'm dutch speaking).
So I guess I will have a problem when someone with an Office in another language opens my file...

 

[Fluff]

I suspect that you need jjjj & uu because of your windows settings, rather than xl, but as I've never used different language versions I'm not sure.