Ask Your Question

Formatting in cells with concatenated data

asked 2019-06-19 13:00:31 +0200

Pete of Ebor gravatar image

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 ?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-06-19 13:08:40 +0200

Opaque gravatar image

updated 2019-06-19 13:10:48 +0200


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 (✔) next to the answer.

edit flag offensive delete link more

answered 2019-06-19 13:47:43 +0200

Pete of Ebor gravatar image

Thanks Opaque. Exactly what I needed.

edit flag offensive delete link more


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

Opaque gravatar imageOpaque ( 2019-06-19 13:54:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-06-19 13:00:31 +0200

Seen: 19 times

Last updated: Jun 19