Concatenate and retain formatting

[geewhysee]

Hi,

I have a spreadsheet that auto imports data and then does further calculations. In cell A1 I have a date/time value in format dd/mm/yyyy hh:mm:ss I have chopped this using the formula =int(A2) in cell K2 with the format dd/mm/yyyy and determined the day using the formula =weekday(K2) in cell Q2 with the format ddd.

I want to bring together K2 and Q2 in cell R2 so that it will show eg

Mon 11/04/2016 for today

However when I use =Q2&" "&K2 I get "2 42471" in R2 obviously it hasn't retained the formatting. I have tried using Format Cells... and typing

ddd dd/mm/yyyy

into custom format type but that has not had an effect I'm guessing it's not a valid format.

Any ideas?

 

[Joe4]

When you Concatenate, it concatenate the "base" values, the values that are really in there (not the formatted values).
Since dates are stored as the number of days since 1/1/1900, you will get values like "42471".

If you want to include formatting, use the TEXT function, i.e.
=TEXT(Q2,"ddd") & TEXT(K2,"dd/mm/yyyy")

 

[geewhysee]

Thanks so much. I figured it was something simple I was missing.