display time

Question

No matter what format I choose, and what number I enter, the display is always 0:00

Answer 1

As it turns out, the failure was my mistake in not entering a colon in the numbers (e.g., 6:00). Once I entered the colon, everything worked fine.

Answer 2

Hello Len Goodwin

If I understand correctly, you are trying to display numbers as time in Excel, but no matter which format you choose, the result keeps showing as 0:00.

Please try selecting the affected cells or the whole column, then go to Home > Clear > Clear Formats. After that, set the cells again to the format you need and re-enter one of the values to test.

As a quick test, you can also type the same number in a completely blank cell or a new workbook. If it displays correctly there, the issue is likely caused by formatting already applied to the original cells. In this case, you can copy the original data using Paste Special > Values only, then set the cells to General or Number to see if that resolves the issue.

Please let me know how it goes after trying these checks, so we can decide on the next steps if needed.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

In Excel, a time value is a fraction of a day. If the display is always 0:00, the underlying value is either 0 or a very small fraction that rounds to 0 when formatted.

Check and correct the following:

1. Verify the cell value
   • Select the cell and look at the formula bar.
   • If the value is an integer (for example 1, 2, 45000) and formatted as time, Excel interprets it as whole days, so 0:00 is shown.
   • To show a time, the value must be a fraction, e.g. 0.5 (12:00), 0.25 (06:00), etc., or entered as a time like 6:00.
2. Enter the time directly
   • Type 6:30, 14:15, etc., and press Enter.
   • Then apply a time format (e.g. h:mm or hh:mm) via Format Cells → Number → Time.
3. Check the custom format
   • If a custom format like h:mm or hh:mm is used, it only controls display; it does not convert numbers to time.
   • If the underlying value is 0, any time format will show 0:00.
4. Confirm the cell is not text
   • If the cell is text (left-aligned, or with a leading apostrophe), Excel will not interpret it as time.
   • Remove any leading apostrophe and re-enter the time, or use Data → Text to Columns to convert text to time.

To display times correctly, ensure the cell contains a valid time value (either entered as a time or as a proper fraction of a day) and then apply an appropriate time format.

---

References:

• Format numbers as dates or times
• Review guidelines for customizing a number format