why does excel mess up combining dates from multiple columns?

[Harry0]

I have 7 dates in each cell, and when I try to combine it to one sell it messes up. Despite only 1 date is shown in each cell for every column. instead of saying 10/2/17 it says 43010 despite i press format cell to show date.
The standard & does not work, or other functions.

The only way is to do this. =IF(CH3<>"",CH3,IF(CI3<>"","")) and so on

Also the latest version of excel is horrible. It goes much slower than an older version despite this newer one is on a more powerful computer. Maybe in future versions it will need super computers to add 1+1 with excel. It is bogged down with so many useless features and does not have basic features.

 

[p45cal]

Supply a workbook to show us what you mean…

 

[David Evans]

I have 7 dates in each cell, and when I try to combine it to one sell it messes up. Despite only 1 date is shown in each cell for every column. instead of saying 10/2/17 it says 43010 despite i press format cell to show date.
The standard & does not work, or other functions.

The only way is to do this. =IF(CH3<>"",CH3,IF(CI3<>"","")) and so on

Also the latest version of excel is horrible. It goes much slower than an older version despite this newer one is on a more powerful computer. Maybe in future versions it will need super computers to add 1+1 with excel. It is bogged down with so many useless features and does not have basic features.

You have 7 dates in each cell? How do you want to combine 7 dates?

There's nothing wrong with Excel as far as I'm aware. It works a lot better with a dose of humility I've found.

 

[Chihiro]

99% of the time, issue is on user side.

Also, as for newer version taking more time. It's the nature of where modern software are headed. In order to make money, developers need to reach broader audience, not just those technically savvy, who may not be familiar with all the inner workings. Thus, they provide animated help, and other features to guide them through.

If these features are not needed, you can certainly turn them off to improve performance. Typically, I turn off all animations and superfluous graphical enhancements etc.

 

[Harry0]

here is a basic thing type in each cell in a1 and a2
10/2/17
10/4/17

in a3 type
=a1&" & "&a2
Result is
43010 & 43012

even pressing format cell to date does not do anything.
if it works on your end congratulations. but it does not work on this mac with the latest excel.

 

[bosco_yip]

here is a basic thing type in each cell in a1 and a2
10/2/17
10/4/17

in a3 type
=a1&" & "&a2
Result is
43010 & 43012

even pressing format cell to date does not do anything.
if it works on your end congratulations. but it does not work on this mac with the latest excel.

In A3 try,

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

Regards

 

[Chihiro]

You need to understand how dates are stored in Excel.

Datetime values are stored in Excel as decimal (double) data type.
With integer portion signifying date portion and decimal value signifying time portion.

When you concatenate, Excel concatenates underlying value, regardless of formatting. To concatenate displayed text, you must use Text function like bosco showed you.

This is intended behaviour of Excel and can be observed in any numeric concatenation.

Ex. A1 = 2.0001 formatted to 2 decimal place (displayed as 2.00)
B1 = 2.3331 formatted to 2 decimal place (displayed as 2.33)

When you use A1 & " & " & B1
result would be 2.0001 & 2.3331

This is likely done to prevent loss of precision.

Edit: To show 2.00 & 2.33 use
=Text(A1, "0.00") & " & " & Text(B1, "0.00")