How to Display Zero Time (00:00) in Excel Using a Custom Time Format

Excel’s default time format hides zero values, showing a blank cell instead of 00:00. This occurs because Excel treats a zero time value as equivalent to zero. You can make zero times visible by applying a custom number format. This article explains how to create and use a custom time format to display 00:00.

Understanding Excel’s Time Format and Zero Values

Excel stores times as decimal fractions of a 24-hour day. The value 0 represents midnight, or 00:00. By default, Excel uses a number format with up to four sections separated by semicolons: positive numbers; negative numbers; zero values; text. The standard time format often lacks a defined section for zero, causing those cells to appear empty. A custom format gives you control over all four sections, allowing you to specify exactly how zero should look.

Before applying a format, ensure your data is a proper time value. You can enter zero time by typing 0:00 or using a formula like =TIME(0,0,0). The cell’s underlying value should be 0. Custom formats only change display, not the actual cell value, so calculations based on the time will remain correct.

Steps to Apply a Custom Format for 00:00

You can apply a custom time format to a single cell, a range, or an entire column. The process uses the Format Cells dialog, which you can access quickly with a keyboard shortcut.

1. Select your cells
   Click on the cell or drag to select the range containing your time data. To format an entire column, click the column header letter.
2. Open the Format Cells dialog
   Press Ctrl+1 on your keyboard. Alternatively, right-click the selected cells and choose Format Cells from the context menu.
3. Navigate to the Custom category
   In the Format Cells dialog, click the Number tab. Select Custom from the category list on the left side.
4. Enter your custom format code
   In the Type field, you will see the current format. Delete it and type one of the following codes:
   hh:mm;; – This shows times (like 14:30) and forces 00:00 to display. It hides any other values like text.
   hh:mm;@ – This shows times and 00:00, and will also display any text you type in the cell.
   You can also use hh:mm;hh:mm;hh:mm;@ for full control, defining positive, negative, and zero sections identically.
5. Apply the format and verify
   Click OK to close the dialog. Any cell in the selection with a zero time value should now display as 00:00. Other time values will appear normally.

Using the Formula Bar for Quick Entry

You can also apply a custom format directly from the Excel ribbon without the full dialog. Select your cells. Go to the Home tab. In the Number group, click the drop-down arrow in the number format box, which may say General. Scroll to the bottom and select More Number Formats. This opens the Format Cells dialog directly to the Number tab. Proceed to the Custom category as described in the steps above.

Common Mistakes and Format Limitations

Cell Still Appears Blank After Formatting

If the cell remains blank, the underlying value is not a true numeric zero. The cell might contain a text string that looks like a time, a space, or a formula returning an empty string. Check the value by selecting the cell and looking at the formula bar. To fix it, re-enter the time as 0:00 or use the TIME function. You can also use the VALUE function to convert text to a number.

Format Shows ###### Instead of 00:00

The ###### symbols mean the column is too narrow to display the formatted content. Even 00:00 requires a minimum column width. To fix this, double-click the right border of the column header. This auto-fits the column to the widest entry. You can also manually drag the column border to make it wider.

Calculations Ignore the Custom Format

Formats only affect visual display. A cell showing 00:00 still has a numeric value of 0. All formulas referencing that cell will use the value 0. This is correct behavior. If you need a text result for reporting, use the TEXT function, like =TEXT(A1, "hh:mm"), which will return “00:00” as a text string.

Custom Time Format Code Comparison

Item	Format: hh:mm;;	Format: hh:mm;@
Displays 00:00 for zero value	Yes	Yes
Displays positive times (e.g., 14:30)	Yes	Yes
Handles text entries in cell	Hides text (cell appears blank)	Shows text exactly as typed
Handles negative time values	Hides negative values	Shows negative values as time
Best use case	Data entry cells where only valid times or zero are allowed	Mixed data where text notes may accompany time entries

You can now make zero time values clearly visible as 00:00 in your worksheets. Use the custom format hh:mm;; for most time-tracking sheets. Remember that the format changes only the display, not the cell’s value for formulas. For more control, explore other custom format codes like [h]:mm to display cumulative hours exceeding 24.