Why are Dates Shown as Hashtags in Excel? Easy Fix!

The notorious string of “hashtags” or pound characters (####) can often cause frustration. This error usually accompanies string and number cells, but it is mostly encountered when working with dates.

In this Excel tutorial, we will take a look at some common causes of why Excel shows hashtags instead of dates and how to correct this.

Table of Contents

Problem: Column Width is Too Small

Irrespective of the type of value in the cell, the most common cause for the hashtag error is a smaller cell width. Values with date formats require the cell to be wide enough to show the whole value.

If it isn’t then you get a bunch of hashtags indicating that Excel cannot show you the whole thing.

Fortunately, this is not a very serious problem. Here are some ways you can get around it:

Solution 1: Widen the Column

The easiest way to solve the column width problem is by altering its width. For this, you can just drag the right edge of the column header until the cell is wide enough for the date to show completely.

Alternatively, you can just double-click on this column edge. This will automatically resize the column to fit the contents of the widest cell in the column.

If the reason for the problem was a narrow column width, after this step, you should see the hashtags disappear, allowing the entire date to be displayed.

Solution 2: Make the Font Smaller

If adjusting the column width is just not an option or you don’t want to tamper with the column width to maintain uniformity, here’s something else you can try.

You can make the font size of the date smaller, so it fits the column, without having to adjust its width.

To make the font smaller, here’s what you can do:

Select the cell containing the date (in our example, we have cell A2).
Right-click on your selection
This will open a popup menu. Select the Format option from this menu. Alternatively, you can select the dialog box launcher (small tilted arrow) from the Font group under the Home tab.
A Format Cells dialog box should open now. Select the Font tab from this dialog box.
Under this tab, you will see categories like Font, Font Style, and Size on the top portion of the window.
Under the Style category, select a size smaller than the one already selected. In our example, the font was originally size 10. We changed it to a size 8.
Click OK to close the Format Cells dialog box.

Experiment with different font sizes to see which one gives the best results. Once your font is small enough to view the entire date, the hashtags should disappear from the cell.

Solution 3: Use the Shrink to Fit Feature

A better option is to allow Excel to decide how small the contents of the cell should be, in order to fit in.

Here’s how you can use the Shrink to Fit feature in Excel:

Select the cell containing the date (in our example, we have cell A2).
Right-click on your selection
This will open a popup menu. Select the Format option from this menu. Alternatively, you can select the dialog box launcher ( ) from the Font group under the Home tab.
A Format Cells dialog box should open now. Select the Alignment tab from this dialog box.
Under the Text Control category, check the box next to the Shrink to Fit option.
Click OK

Excel’s Shrink to fit feature usually makes sure that the contents are small enough to fit the cell and just large enough to be viewable.

So if the font size was the problem, your hashtags should now disappear and you should be able to see the full date.

Solution 4: Convert to Short Date

Sometimes, the format of the date may be too long to fit the cell. For example, a date in Long Date format usually displays the entire date, with the full day of the month and month of the year.

For example, the date “Sunday, January 6, 2019” is in Long Date format. This is usually too long to show in an average width cell.

To avoid this, you can convert the date to Short Date format, as follows:

Click on the Home tab
Under the Number group, you can see a dropdown for the Number Format. This lets you choose the format for your number, date, and time values. Click on this dropdown menu.
This will display a list of Number formatting options. Select the Short Date option from the list.

This should convert your date format from Long Date to Short Date, and your date should be shortened to just the number representations for a date, month, and year.

If the Long Date format was the reason for the appearance of hashtags, the above steps should get rid of the error and display the shortened version of the date.

Problem: There is a Negative Number in the Date

Another reason for the hashtag error could be the presence of a negative number in the date. This might happen accidentally or because a formula resulted in a negative number.

Dates in Excel are, in essence, stored as numbers. They are simply formatted to be displayed as dates. The numbers that represent the dates are integers, known as Serial Numbers.

Serial numbers start from January 1st, 1900. For each day after that, the serial number increases by 1.

So, serial number 1 represents the date 1/1/1900, serial number 2 represents the date 1/2/1900, and so on. So the date 1/6/2019 actually refers to the serial number 43471, because this date is 43,471 days after 1/1/1900.

To see the serial number that date represents, you need to change its format from Date to Number.

To find out if your hashtag error is due to a negative serial number in date format, here’s what you need to do:

Select the cell containing the hashtag error (in our example it is cell A2).
Click on the Home tab
Under the Number group, you can see a dropdown for the Number Format. This lets you choose the format for your number, date, and time values. Click on this dropdown menu.
This will display a list of Number formatting options. Select either the Number or General option from the list.
This will display the serial number underlying the date in cell A2.

If it is a negative number, it’s clear that this is the reason for the error.

Solution 1: Remove the Minus Sign

This is an obvious solution. If the minus sign was accidentally put in, then simply remove it and see if it makes the hashtag error disappear.

Solution 2: Fix the Formula so you do not get a Negative Result

Oftentimes when you subtract dates from one another in a function or formula, you might end up getting a negative result. This usually happens when you accidentally try to subtract a newer date from an older one.

If the negative number came as a result of a formula, all you need to do is check your formula and make the correction. Remember to:

Verify that all dates and times involved in the formula are positive values.
Reverse the subtraction if you find that your formula is subtracting a newer date from an older one.
See if all components of the formula or function are in the expected formats.

Problem: Date Value is Outside Excel’s Acceptable Value

Excel has been designed to handle only dates that come between 1/1/1900 and 12/31/9999. This also means it can only handle serial numbers between 1 and 2958465.

Any date or serial number that is beyond this range usually results in a hashtag error.

Solution 1: Change the Date or Serial Number to Fit the Range

An obvious solution to this problem is to check if the serial number or date is a valid one and change the date or serial number to one that is within range.

Solution 2: Change the Format of the Cell

If you don’t want to change the serial number or date, you can change the format of the cell from Date to some other format, like Text or General. For this, follow these steps:

Select the cell containing the hashtag error (in our example it is cell A2).
Click on the Home tab
Under the Number group, you can see a dropdown for the Number Format. This lets you choose the format for your number, date, and time values. Click on this dropdown menu.
This will display a list of Number formatting options. Select the Text option from the list.
This will convert the Date value to Text format.

Alternatively, you can convert the date to text by simple adding a leading apostrophe to the date, like this:

When you press the return key, your hashtag error should disappear. This method, however, is usually discouraged by most Excel experts, as it creates a lot of issues if the cells are used in further calculations or formatting.

Solution 3: Check if there is an error in the formula resulting in an Out-of-Range Result

If the error is the result of a formula, then check to see what part of the formula is causing it.

Check the values in the cells that are referenced in the formula and find out if the formula’s calculations are logically correct.

In this article, we saw some common reasons you might be getting hashtag errors in place of your date values.

We also explained how you can find out what the cause of the error is and the different ways in which you can try to solve the problem. We hope this was helpful in diagnosing your date hashtags problems

Other Excel articles you may like: