Excel TEXT Function for Narrow Date Columns

The issue is not the visible number of characters alone. Excel calculates whether a formatted date fits using the cell’s display layout, including line breaks, font metrics, and the custom number format. With a multi-line date format such as mm/dd/yy ddd hh:mm AM/PM, Excel 365 can return ####### even when a visually similar single-line value appears to fit. This affects project trackers that need dates, weekdays, and times in narrow columns.

The problem

This came up in the r/excel community discussion about narrow date columns. The worksheet used a monospace font and tightly packed columns so a team could scan project due dates quickly. The goal was to show the full date, day of week, and time without giving the column extra width.

The custom number format was:

mm/dd/yy ddd hh:mm AM/PM

The same information displayed across multiple lines, but Excel treated the cell as too narrow at certain column widths. It replaced the value with #######, even though a single-line version containing spaces could fit in a narrower column.

Excel commonly displays hash characters when a number or date cannot fit in the available column width. Microsoft documents widening the column as the standard fix. That does not solve this particular layout problem because the worksheet has many columns and the available width is intentional.

The likely explanation is a limitation, or longstanding formatting bug, in how Excel measures custom-formatted dates that contain line breaks or carriage returns. Excel does not simply count visible characters. It measures the rendered result according to the cell’s formatting rules. A line break may reduce the visible width for a person while still producing an unexpected width calculation inside Excel.

The behavior has been reported in Excel 365 on Windows Desktop, including Version 2610, Build 20527.20088, Click-to-Run. It is safest to treat the custom multi-line number format as unreliable when every fraction of column width matters.

The solution

The most dependable workaround is to separate the stored date and time from the value shown to the user.

Keep the original date-time value in one column. Use a second display column with the TEXT function:

=TEXT(A2,"mm/dd/yy ddd hh:mm AM/PM")

Here, A2 contains the real Excel date and time. The text inside quotation marks tells Excel exactly how to display it. mm/dd/yy shows the date, ddd shows the abbreviated weekday, hh:mm shows the time, and AM/PM adds the time period.

Microsoft describes TEXT as a way to apply a format code to a number and return a more readable display value. The important tradeoff is that the result is text, not a numeric date-time. Sorting, filtering, calculating durations, and comparing deadlines should use the original date column, not the display column.

This design is more robust than trying to force Excel to interpret a multi-line custom number format correctly. It also makes the workbook easier to maintain. The data column remains suitable for formulas, while the display column is optimized for quick reading.

A second option is a macro that adjusts column widths or applies a preferred layout automatically. Excel’s VBA AutoFit method changes a column or range to fit its contents. However, that approach is less suitable here because the goal is to preserve a deliberately narrow worksheet. It can also behave differently when columns are grouped, so it should be tested before being used in a shared workbook.

The most practical recommendation is therefore:

• Store the true date-time value in a hidden or helper column.
• Display a text version in the narrow project-tracking column.
• Use the hidden value for calculations, sorting, and alerts.
• Avoid relying on the multi-line custom number format for critical dates.

How to apply it

1. Insert a helper column next to the visible project date column.
2. Keep the original date and time in the helper column. Confirm that Excel recognizes the value as a date-time by changing its format temporarily to General. A numeric value indicates that Excel is storing it correctly.
3. In the visible tracking column, enter:
   =TEXT(A2,"mm/dd/yy ddd hh:mm AM/PM")
   Replace A2 with the cell containing the original date-time.
4. Fill the formula down for all projects.
5. Apply the same monospace font used elsewhere in the worksheet. Because the output is now text, Excel will display the exact characters returned by the formula rather than trying to measure a complex multi-line number format.
6. Reduce the column width gradually until the text reaches the desired limit. If the complete value no longer fits, shorten the display format rather than hiding part of the deadline.
7. For a shorter version, use:
   =TEXT(A2,"mm/dd/yy ddd hh:mm")
   This removes the AM or PM suffix while preserving the date, weekday, and time.
8. Use the original date-time column for calculations. For example, to find the number of days between two project dates, subtract the stored values, not the TEXT results.
9. Sort the project list by the original date-time column. Sorting the display column can sort the values alphabetically because TEXT returns text.
10. Hide the helper column if the team only needs the compact view. Do not delete it, because it is the reliable source for formulas, filters, and future automation.
11. If the workbook uses formulas that refer to the old visible date column, update those formulas to point to the helper column.
12. Test the workbook at several widths, including the narrowest width used by the team. Check due dates, overnight times, blank cells, sorting, and filters before rolling the layout out.

If the team needs a genuinely multi-line visual, another option is to place separate values in separate rows or columns, such as date in one column and time in another. That changes the layout but avoids asking one cell to combine a complex date format with aggressive width constraints.

Implementation angle

A project tracker can use the stored date-time column as the system field while the compact TEXT column remains a presentation field. An automation partner could move the underlying project data into Zoho Creator, then sync due dates and status changes through Make or n8n while preserving the Excel view for quick team review. Claude can also help audit formulas and identify places where a text display value has accidentally been used in a calculation.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com to turn a fragile Excel project tracker into a reliable workflow.

Frequently asked questions

Why does Excel show hash marks for a date that looks like it should fit?

Excel measures the formatted cell, not just the characters you can see. Line breaks and custom date formats can make that measurement behave unexpectedly, especially in narrow columns.

How do I show a date and time in a narrow Excel column?

Keep the real date-time value in one column and use a second column with a formula such as =TEXT(A2,"mm/dd/yy ddd hh:mm AM/PM"). Adjust the format to be shorter if the full text does not fit.

Can I sort or calculate dates from a TEXT formula in Excel?

TEXT returns text, so sorting its results may be alphabetical rather than chronological. Use the original date-time values for sorting, filtering, comparisons, and calculations.