How To Fix Dates Showing As Numbers Or ##### In Excel (2026)
A row of ##### in an Excel cell, or a date that suddenly turns into a plain number, looks like lost data but usually is not.
In most cases the column is too narrow, the cell has the wrong number format, or a formula returned a negative date or time.
This guide follows Microsoft’s support pages and shows each cause with the exact steps to fix it.
Quick answer: match the symptom to the fix
| What you see | Likely cause | Fix |
|---|---|---|
| ##### in a date or number cell | The column is too narrow | Double-click the right edge of the column header, or drag it wider |
| ##### in a cell with a long date | The date format is too long for the column | Home > Number Format > Short Date |
| ##### in a date or time formula result | The result is a negative date or time | Correct the formula so the result is not negative |
| A date shows as a number code | The cell uses the General format | Select the cell, press Ctrl+1 and choose Date in Category |
Fix ##### caused by a narrow column
Excel shows ##### when a column is not wide enough to display the cell’s contents.
- Move the pointer to the right edge of the column header.
- Drag the edge to the width you want, or double-click it to fit the contents.
The cell value itself is unchanged, so the date or number comes back as soon as the column is wide enough.
Use AutoFit or set an exact width
Microsoft describes menu commands for fitting a column as well as the mouse method.
| Goal | Steps |
|---|---|
| Fit one or more columns to their contents | Select the columns, then Home > Format > AutoFit Column Width |
| Set an exact width | Select the columns, then Home > Format > Column Width, enter a value and select OK |
| Fit every column on the sheet | Click Select All, then double-click any boundary between column headings |
| Copy another column’s width | Copy a cell in that column, right-click a cell in the target column, then Paste Special > Keep Source Column Widths |
A column can be set from 0, which hides it, up to 255, and the default width is 8.43 characters.
Shrink the content instead of widening the column
When you cannot change the column width, Microsoft lists three other options.
| Option | How |
|---|---|
| Shrink to fit | Select the cell, open the Alignment Settings launcher on the Home tab and tick Shrink to fit |
| Fewer decimal places | Home > Decrease Decimal for a number with too many decimals |
| A shorter date | Home > Number Format arrow > Short Date |
Fix ##### caused by a negative date or time
Excel also shows ##### when a cell holds a negative date or time, which can happen when a formula returns one.
- With the 1900 date system, make sure your dates and times are positive.
- Use correct formulas to subtract dates or to add and subtract time so the result is not negative.
- If a negative formula result is formatted as a date or time, apply a different number format.
Widening the column will not help in this case, so check the formula before you resize anything.
Change how a date or time is displayed
If a date appears as a number, or in a style you do not want, set the format yourself.
- On the Home tab, open the Number dialog launcher, or press Ctrl+1.
- In the Category list, select Date or Time.
- In the Type list, select the format you want.
- Optionally pick a Locale to show the format used in another region, and check the Sample box.
Formats that start with an asterisk follow the regional date and time settings in Control Panel, and formats without one do not.
Undo a date format that turned into a number
Selecting General in the Category list removes the date format and shows the number code underneath.
That is why a date cell set to General looks like a plain number.
To bring the date back, reapply a Date format, or re-enter the date so Excel applies its default date format again.
To keep a specific format such as January 2010 exactly as typed, select Text in the Category list instead.
Build a custom date format
Choose Custom in the Category list, start from the built-in format closest to what you want, and edit the code in the Type box.
| To display | Code |
|---|---|
| Month as 1 to 12 | m |
| Month as 01 to 12 | mm |
| Month as Jan to Dec | mmm |
| Month as January to December | mmmm |
| Day as 1 to 31 | d |
| Day as Sun to Sat | ddd |
| Day as Sunday to Saturday | dddd |
| Year as 00 to 99 | yy |
| Year as 1900 to 9999 | yyyy |
The letter m means minutes instead of month when it comes right after h or hh, or right before ss.
If the format includes AM or PM, Excel uses a 12-hour clock, and without them it uses a 24-hour clock.
Show times with AM or PM
Time codes follow the same rule, and the AM or PM marker switches the hour to a 12-hour clock.
| To display | Code |
|---|---|
| Hour with AM or PM, such as 4 AM | h AM/PM |
| Time such as 4:36 PM | h:mm AM/PM |
| Time such as 4:36:03 P | h:mm:ss A/P |
Typing 9:30 a or 9:30 p into a cell displays 9:30 AM or 9:30 PM.
Show elapsed time
| To display | Code |
|---|---|
| Elapsed hours, such as 25.02 | [h]:mm |
| Elapsed minutes, such as 63:46 | [mm]:ss |
| Elapsed seconds | [ss] |
| Fractions of a second | h:mm:ss.00 |
Handy date shortcuts
| Task | Shortcut or formula |
|---|---|
| Apply the default date or time format | Select the cell and press Ctrl+Shift+# or Ctrl+Shift+@ |
| Enter today’s date as a fixed value | Select an empty cell and press Ctrl+; |
| Enter a date that updates | Type =TODAY() and press Enter |
The =TODAY() date refreshes each time you reopen the worksheet or recalculate it.
Typing 2/2 displays 2-Feb by default, and the default format follows your regional settings.
Related Excel guides
- Convert numbers stored as text in Excel when a value will not behave like a number.
- Fix Excel formulas not calculating or showing text if a date formula shows the formula itself.
- Fix #N/A, #VALUE! and #REF! errors in Excel for other error messages in cells.
Official sources
- How to correct a ##### error (Microsoft Support)
- Format numbers as dates or times (Microsoft Support)
- Change the column width and row height (Microsoft Support)