Formatting Issues

How to Keep Excel Date and Time Entries Displayed as Time Only

Steve KSteve K Nov 3, 2026 868 views

Question details

The user wants to format specific cells so they only display the time portion of a date-time entry, preventing the spreadsheet from reverting to a full date and time format.

How to Keep Excel Date and Time Entries Displayed as Time Only
Product
Excel
Device & OS
not provided
Scenario
Entering new date and time values into spreadsheet cells that are intended to exclusively show the time.
Observed behavior
Excel automatically changes the cell's number format to show the full date and time upon entering a new value, even if previous values in the same range display only the time.
Before you start

Verify that your entered data is recognized as a valid date and time value by selecting the cell and checking the formula bar. If it shows seconds or a full date sequence, it is recognized as a serial number rather than text.

Solution 1Recommended

Apply an Explicit Custom Time Format

Lock the cell format to display only hours and minutes using a specific custom formatting code, preventing automatic overrides.

Excel interprets entered text that resembles dates or times based on your system's regional settings. If a cell has a 'General' or non-explicit format, the application may automatically apply a default full date-and-time layout.

By applying a specific custom format like hh:mm;@, you command the spreadsheet to consistently interpret and display the serial number strictly as hours and minutes.

1
Select the target cells

Highlight the specific cells, rows, or columns where you want to enter your time values.

2
Open the Format Cells dialog

Right-click the highlighted area and select 'Format Cells' from the context menu, or press the Ctrl + 1 shortcut on your keyboard.

3
Navigate to Custom formatting

In the Format Cells window, go to the 'Number' tab and select 'Custom' from the Category list on the left.

4
Enter the format code

In the 'Type' input field, enter the code hh:mm;@ and click the 'OK' button to apply the formatting to your selected cells.

Apply an Explicit Custom Time Format
Format Successfully Locked: The applied hh:mm;@ format ensures that future entries in these cells will retain the time-only display, and any text entered by mistake will be displayed exactly as typed.
Efficient Spreadsheet Formatting

Format Dates and Times Easily with WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive interface for managing custom number formats. You can effortlessly lock cell formatting to display exactly what you need without frustrating automatic changes.

  1. 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your time data.
  2. 2. Access cell formatting: Select the desired cells and press Ctrl+1 to quickly bring up the Format Cells dialog.
  3. 3. Apply custom format: Under the Number tab, select Custom, type hh:mm;@ in the Type box, and click OK.
100% compatible with Microsoft Excel custom formatting codes.Intuitive Format Cells dialog with rich built-in presets.Lightweight, fast, and completely free to use.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically change my time format to date and time?

Excel attempts to be helpful by converting recognized date or time entries into serial numbers based on your system's regional settings. If the cell's format is set to 'General', Excel automatically applies a default system date-time format rather than maintaining a time-only view.

How do I know if my time entry is stored as text or a valid time?

Select the cell and look at the formula bar at the top of your workspace. If the formula bar displays the time with seconds (e.g., 10:30:00 AM) or a full date, it is a valid date-time serial value. If it lacks seconds and looks exactly as you typed it, it is likely stored as text.

What does the @ symbol do in the custom format code hh:mm;@?

The @ symbol in custom number formatting serves as a text placeholder. Adding it ensures that if you accidentally enter plain text into the cell instead of a numeric time, the text will be displayed exactly as you entered it, while valid time numbers will be formatted to show only hours and minutes.