Displaying a date is actually displaying a number format that looks like a date. It is not text. So numbers are always truncated with ##### when the column width is too narrow to display the number, in your case the date.
You can double click between the column letters to auto size the column to match widest cell content or you can convert you date value to a text value using function =Text()
i.e. =Text(TheDate, "mmm d, yyyy")
Text will expand past it's cell origins if there is room to do so. Numbers stay within their column/widths.
Loading
ExcelTips (Ribbon Interface)
Members
This Site
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
Author Bio
With more than 35 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Helpful Links
ExcelTips FAQ
Ask an Excel Question
Make a Comment
Free Business Forms
Free Calendars
Subscribe
Hash Marks Displayed Instead of Cell Contents
Bob is having some problems getting Excel to display the text within a cell. What is happening is that Excel is displaying a series of # signs instead of the text. He notes that he is not even close to the character limit of the cell.
The answer here depends on what you mean by "the character limit of the cell." Generally, such a statement means that you haven't reached the limit of the text that Excel can store in the cell—approximately 32,000 characters. It is important to keep in mind that what Excel can store and what it can display are two different things, as will shortly be discussed. If, however, by "character limit" you mean that the cell is wider than what is stored in the cell, that is a separate issue.
First things first: Excel can store about 32,000 text characters in a cell, but it can only display up to 255 characters if the cell is formatted as text. If the cell contains more than 255 characters and the cell is formatted as text, then the hash marks are displayed. The solution is to change the format of the cell to general; then the text will display as you expect.
The more common occurrence is to see hash marks displayed when the cell contains a numeric (or date) value. If the cell is too narrow to display the value, then the hash marks are shown. They indicate that an "overflow" condition has occurred and that your value cannot be displayed as you want.
This is particularly common when displaying dates using a format that requires more horizontal space. For instance, if you display a date as "September 22, 2010," that date takes more column width to display than does "9/22/10." The solution is to simply widen the column so that the display doesn't overflow the width.
Dates will also display hash marks if you attempt to display a date value outside the range of dates that Excel can handle (1/1/1900 through 12/31/9999).
You should also note that you might see hash marks appear if you change the size of the font used in a cell. Change the font to a larger size, and Excel may not be able to display the value horizontally. If you can't widen the column then consider making the font smaller so that Excel can make the full value visible.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8444) applies to MS Excel versions: 2007 2010
You can find a version of this tip for the older menu interface of Excel here: Hash Marks Displayed Instead of Cell Contents.
Related Tips:
Your Data, Your Way! Want the greatest control possible over how your data appears on the page? Excel's custom formats can provide that control, and ExcelTips: Custom Formats can unlock the secrets to creating your own custom formats. Check out ExcelTips: Custom Formats today!
Comments for this tip:
Aldo Santolla 03 May 2013, 15:42
Mike Zengerling 02 May 2013, 09:26
Nikki, based on your description, it sounds like your formatting is changing; thus the problem you are experiencing. The following ideas may be helpful…
1. Try copying the format at the top of the column and paste format down to the end of your data. As long as all the data in the column is consistently numeric and not alpha/numeric, this method usually helps.
2. This method forces the data to be strictly numeric and does not assume the original data is consistent and possibly, why you are getting the hash mark problem. Try the following…
• Assume your data is in Column B.
• If Column C is not blank, insert a column next to Column B.
• Apply your desired DATE format to Column C.
• In Cell C1, enter a header for the column.
• In Cell C2, enter the following formula (without quotes): “=VALUE(B2)”
• Copy the formula to the end of the data in Column B.
• Select either the entire Column C or just the data range you just created.
• Copy the column or data and paste as value, thus replacing the formulas as values only.
• Finally, delete Column B. That is all you should need for this method.
There may be easier ways to go about fixing your data; however, I have found when working with large amounts of data, I prefer to make sure everything is consistent to what I expect it to be throughout the worksheet. Either of the methods above can afford you that.
I hope this helps.
1. Try copying the format at the top of the column and paste format down to the end of your data. As long as all the data in the column is consistently numeric and not alpha/numeric, this method usually helps.
2. This method forces the data to be strictly numeric and does not assume the original data is consistent and possibly, why you are getting the hash mark problem. Try the following…
• Assume your data is in Column B.
• If Column C is not blank, insert a column next to Column B.
• Apply your desired DATE format to Column C.
• In Cell C1, enter a header for the column.
• In Cell C2, enter the following formula (without quotes): “=VALUE(B2)”
• Copy the formula to the end of the data in Column B.
• Select either the entire Column C or just the data range you just created.
• Copy the column or data and paste as value, thus replacing the formulas as values only.
• Finally, delete Column B. That is all you should need for this method.
There may be easier ways to go about fixing your data; however, I have found when working with large amounts of data, I prefer to make sure everything is consistent to what I expect it to be throughout the worksheet. Either of the methods above can afford you that.
I hope this helps.
Nikki 01 May 2013, 10:02
None of these tips helped. I am having the problem with dates. The dates appear as entered in columns then suddenly they stop and start with the hash tags. I have checked the cell formatting and see no differences between the cells that are working properly and inserting the dates and those that don't work and insert the hash tags. I am using current dates so they are not outside the range of dates you mention. I have xpanded the width of the cells well beyond what they need to be - no good. UGH!