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 seeLikely causeFix
##### in a date or number cellThe column is too narrowDouble-click the right edge of the column header, or drag it wider
##### in a cell with a long dateThe date format is too long for the columnHome > Number Format > Short Date
##### in a date or time formula resultThe result is a negative date or timeCorrect the formula so the result is not negative
A date shows as a number codeThe cell uses the General formatSelect 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.

  1. Move the pointer to the right edge of the column header.
  2. 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.

GoalSteps
Fit one or more columns to their contentsSelect the columns, then Home > Format > AutoFit Column Width
Set an exact widthSelect the columns, then Home > Format > Column Width, enter a value and select OK
Fit every column on the sheetClick Select All, then double-click any boundary between column headings
Copy another column’s widthCopy 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.

OptionHow
Shrink to fitSelect the cell, open the Alignment Settings launcher on the Home tab and tick Shrink to fit
Fewer decimal placesHome > Decrease Decimal for a number with too many decimals
A shorter dateHome > 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.

  1. With the 1900 date system, make sure your dates and times are positive.
  2. Use correct formulas to subtract dates or to add and subtract time so the result is not negative.
  3. 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.

  1. On the Home tab, open the Number dialog launcher, or press Ctrl+1.
  2. In the Category list, select Date or Time.
  3. In the Type list, select the format you want.
  4. 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 displayCode
Month as 1 to 12m
Month as 01 to 12mm
Month as Jan to Decmmm
Month as January to Decembermmmm
Day as 1 to 31d
Day as Sun to Satddd
Day as Sunday to Saturdaydddd
Year as 00 to 99yy
Year as 1900 to 9999yyyy

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 displayCode
Hour with AM or PM, such as 4 AMh AM/PM
Time such as 4:36 PMh:mm AM/PM
Time such as 4:36:03 Ph: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 displayCode
Elapsed hours, such as 25.02[h]:mm
Elapsed minutes, such as 63:46[mm]:ss
Elapsed seconds[ss]
Fractions of a secondh:mm:ss.00

Handy date shortcuts

TaskShortcut or formula
Apply the default date or time formatSelect the cell and press Ctrl+Shift+# or Ctrl+Shift+@
Enter today’s date as a fixed valueSelect an empty cell and press Ctrl+;
Enter a date that updatesType =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.

Official sources

Leave a Reply

Your email address will not be published. Required fields are marked *