combining dates and text problem getting different formats

juke_juke13

Board Regular
Joined
May 25, 2004
Messages
57
On one worksheet I am combing the date 3/31/2001 and another cell "Hello". I am also doing the same thing on another worksheet.

I need it to be consistent when It combines the two either through the & function or through concatenate.

the probelm is that on one sheet it will display 3/31/2001Hello and on another worksheet in the same book it will display 36981Hello.

Grrr this is very aggravating! Please help
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Dates are really stored as numbers (the number of days since 1/1/1900). So if you want to maintain the formatting, use the TEXT function on the Date part, i.e.

=TEXT(A1,"m/d/yyyy") & B1
 
Upvote 0
Are you sure you have 2 'real' dates?

If the first sheet returns 3/31/2001Hello it sounds like the date might actually be text.

For the other sheet you could use something like this.

=TEXT(A1, "m/d/yyyy) & A2
 
Upvote 0
You guys are awesome! quick replies and obviously they worked! What gets me is that i don't really understand why. If someone wants to explain that would be sweet. I have both date columns (on each sheet) stored as date (formatted in the default excel date format m/d/yyyy). The hello is formatted as general in both worksheets. So what is the difference that is causing one to revert to the 1900 calculation and the other one to be recognized as a date...or text as it were?
 
Upvote 0
One is a number:
Can be calculated and referenced independent of format.

The other is Text:
Can not be calculated upon since it is not a number.
 
Upvote 0
To easily see if a value is highlighted as a Date or Text, change the cell format to General. If it turns into a 5 digit number, it is stored as a Number (which is what Dates are stored as). If it is stored as Text, it will still look like a Date.

Note, if it is stored as Text, simply changing the cell format to Date will NOT change how that value is stored. You must then also convert (you can use Text to Columns to do so).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top