Formatting in cells with concatenated data

I have a spreadsheet where some of the cells show a date from another sheet. I want the date to show in the format ddd dd mm (eg Wed 19 Jun) The cells do this at present. If I then wish to concatenate other data into that cell eg I wish to add the text " - Rain" so the cell should read “Wed 19 Jun - Rain”, when I do this, the date element reverts to its integer value and the cell shows “43635 - Rain” instead. Is there any way to maintain the date formatting when adding other data into the cell ?


assuming cell A1 contains your date and cell B1 contains “Rain”:

=CONCATENATE(TEXT(A1,"ddd dd mm")," - ", B1)

The fact is, that nothing “reverts”. Dates are stored as integers in Calc. Formatting only changes the representation of this internal integer number. That’s why you need to repeat the formatting, if you want to use it in text strings.

If the answer is correct or helped you to fix your problem, please click the check mark (:heavy_check_mark:) next to the answer.

Thanks Opaque. Exactly what I needed.

Great - Please use “add a comment” instead of “Your Answer” if not answering a question but commenting an answer given. Thanks in advance …