Formatting Issues

How to Stop Excel from Converting 39. into a Time Format

Chanuka Geekiyanage Oct 10, 2026 871 views

Question details

The user needs to prevent Excel from automatically reformatting numbers that end with a period (such as "39.") into time or date serial values.

How to Stop Excel from Converting Numbers with Periods into Time

Product: Microsoft Excel
Device & OS: not provided
Scenario: Entering numerical data with a trailing decimal point or period into a spreadsheet cell.
Observed behavior: Excel interprets an entry like "39." as a time or date value and automatically changes the display to formats such as "39.00.00" or "01/01/1900 15:00:00".

Before you start

Before modifying cell formats, determine if the entered values need to be used in mathematical calculations (Number format) or if they act as plain text labels (Text format).

Solution 1Recommended

Change Cell Format to General or Number

Revert the automatically applied time format back to a standard number format so the value displays properly.

When Excel guesses the data type incorrectly, it alters the cell's underlying format to match its assumption. Changing the format back to General or Number will remove the time appearance, though you may need to retype the value to ensure accuracy.

Select the cell

Click on the cell that is displaying the incorrect time format.

Open Format Cells

Right-click the selected cell and choose "Format Cells" from the context menu.

Choose Number or General

Under the Number tab, select "General" or "Number" from the category list, then click OK.

Re-enter the data

Delete the incorrect time value and type "39." into the cell again.

Tip: If you are fixing a large column of data, you can select the entire column before right-clicking to apply the Number format to all cells at once.

Solution 2

Pre-format Cells as Text

Format empty cells as Text before entering data to completely bypass Excel's automatic number-to-time conversion.

Advanced Data Formatting

Take Control of Your Data Formatting with WPS Spreadsheet

WPS Spreadsheet offers an intuitive interface and precise cell formatting controls, making it effortless to stop unwanted auto-conversions. Enter your data exactly how you intend to without frustrating formatting shifts.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
2. Select your cells: Highlight the cells where you need to enter data like "39.".
3. Format as Text: Right-click, select "Format Cells", and choose "Text" under the Number tab.
4. Input data seamlessly: Type your numbers; WPS will preserve the exact characters and periods without converting them to time.

Easily prevent unwanted date and time auto-conversions with simple text formatting.100% compatible with Microsoft Excel file formats (.xls, .xlsx, .csv).Lightweight, fast, and completely free to use for everyday data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically change numbers with periods to time?

Excel contains built-in auto-formatting rules designed to speed up data entry for dates and times. When it detects patterns that resemble time conventions (like numbers separated by periods or colons), it assumes you are trying to input a time and changes the cell format accordingly.

Can I use an apostrophe to stop the conversion?

Yes. Typing a single apostrophe (') right before your number (e.g., '39.) forces Excel to treat the entry as text. The apostrophe will not appear in the printed or displayed cell, but the data will not be converted.

How do I fix multiple cells that have already converted to dates or times?

Highlight all affected cells, right-click, and select 'Format Cells'. Change the format category to 'General' or 'Number'. Note that you may need to manually correct the values if the auto-conversion significantly altered the underlying serial number.

Can I disable automatic date and time conversion in Excel completely?

In newer versions of Excel, you can go to File > Options > Data and uncheck the automatic data conversion settings. For older versions, pre-formatting the cells as Text before typing is the most reliable workaround.

Document & File Issues