Changing numbers to dates

[Graham C1600]

Hi,

I'm currently using data from 1 excel sheet and then moving it to another sheet using the &" "& option. Something like detailed below :-

	A	B	C	D
1	Start date	05/05/2018	End date	15/05/2018

<tbody>
</tbody>

I then use this information and move it to another sheet but put the text from all 4 cells into 1 cell by typing the following into the required cell on the new sheet:-
=A1&" "&B1&" "&C1&" "&D1

The only issue with doing this is that is provides something like the following back in the new cell (Start date 32456 End date 34512)]

So obviously when pulling the information across the date is changing into the numbers.

Is there a way of stopping this happening so the actual dates will be displayed instead of numbers ?

Thanks

 

[Joe4]

So obviously when pulling the information across the date is changing into the numbers.

Its actually not changing anything. That is how Excel stores dates, as numbers (specifically, the number of days since 1/0/1900).
It is the date format that makes them appear that way. If you take any date, and change the format to "General", you will see it as Excel does.

So, if you are building a string, and want them displayed in a Date format, you can use the Text function to do that, i.e.

[COLOR=#333333]=A1 & " " & TEXT(B1,"dd/mm/yyyy") & " " & C1 & " " & TEXT(D1,"dd/mm/yyyy")

[/COLOR]

 

[Graham C1600]

Many thanks for the prompt reply. Your a star.

 

[Joe4]

You are welcome.
Glad to help!