[Solved] Show date results

[Fitch]

Hi all.
I have to set up a list of meeting which would look like below (last year's)
Jan 2022 .... AGM ........ Sun 30 January 2022
Feb 2022 .... Workshop .... Sat 26 February 2022
Mar 2022 .... Workshop .... Sun 27 March 2022
Apr 2022 .... Meeting ........ Sat 23 April 2022

So I set up the dates which happen to be the last weekend of every month - odd months Saturdays, even Sundays.
Using formulas to get the right days, and then formatting the cells to show the contents as above.
Then I add all the cells together into one cell with:

Code: Select all

=G4&" "&H4&" ... "&I4&" ... "&M4&" "&N4

Of course, I get:
44955 2023 ... AGM.... ... 1 44955
44982 2023 ... Workshop ... 7 44982
45011 2023 ... Workshop ... 1 45011
45045 2023 ... Meeting ... 7 45045

So my question is:
How do I show the various text outputs instead of the raw date numbers in the final cells?
Thanks.
Fitch.

[RusselB]

You can get rid of the M4 column and get your wanted results by wrapping the G4 and N4 references in the TEXT function.
Check the Help file for more details.

[RoryOF]

Format your cells as Dates and choose he appropriate display from the formatting dialog.

[RusselB]

Rory: The formatting of the cells is irrelevant when forcing a concatenation of cells as indicated in the OP's provided formula.

[Fitch]

I did look at the text function previously, but couldn't figure what I put in as the "Format".
I presume the number is the cell, e.g. G4 for the first bit

[Villeroy]

The second argument is a format code as displayed in the number format dialog.

=TEXT(G4;"NN DD MMMM YYYY") returns the string "Sun 30 January 2022" which can be concatenated with any other string.

[Fitch]

Ooooh! MMM
Now I'm getting the hang of it
Just posted a few seconds after you did Villeroy.
Thanks everyone.

[Villeroy]

Select a numeric cell and play with the number format dialog. This should help with simple formats. There is also extensive documentation for more complex number formats. The number format codes are the same that are used as a second argument to the TEXT function which returns the text that would be displayed when a numeric cell would be formatted like this. A number format does not change the numeric type of a cell wheras the TEXT function actually returns text.

[Fitch]

Got it
Thanks.
I'm sure I'll have many minutes of fun with this....

[RoryOF]

Rory: The formatting of the cells is irrelevant when forcing a concatenation of cells as indicated in the OP's provided formula.

I was answering the main problem of all his dates being numbers.