How to use Excel to join cells and the difficulty of joining dates

A common thing to do in Excel is to join the contents of two (or more) cells. 

Assuming cell A1 contains the word 'Joe' and cell A2 contains the word 'Bloggs' than in cell A3 you could join the two cells using the formula ...

= A1 & A2

... and A3 will show


This isn't ideal. So let's add a space in between and change the formula in cell A3 ...

= A1 & " " & A2

... and now A3 will show

Joe Bloggs

That's better. But a funny thing happens when you try to join a string and a date. So now let's assume that cell A1 contains 'As at' and A2 contains '18/04/2016'. If cell A3 still has the formula ...

= A1 & " " & A

... then cell A3 will show ...

As at 42478

That's not what we wanted. This happens because Excel represents dates Excel stores dates as a number that represents the number of days since the beginning of the year 1900. In our example, there have been 42,478 days since the start of 1900.

We can fix this by wrapping the reference to the cell that has the date in it (i.e. A2) in Excel's TEXT function. So now if A3 is changed to be ...

= A1 & " " & TEXT(A2, "dd/mm/yy")

... then A3 will show ...

As at 18/04/16

That's better. We can change the second part of the TEXT function to format the date differently. For example: 

=A1 & " " & TEXT(A2,"d mmm yy") will show As at 18 Apr 16

=A1 & " " & TEXT(A2,"ddd dd mmm yyyy") will show As at Mon 18 Apr 2016

=A1 & " " & TEXT(A2,"dddd dd mmmm yyyy") will show As at Monday 18 April 2016

The are loads of options to format the text of dates but hopefully this is enough to get you started.