How to Automatically Format a Number as an Hour in Excel
Question details
The user wants to enter a whole number like 9 or 13 into a cell and have it automatically display as a time value like 9:00 or 13:00, without manually typing a colon or using a separate formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering large volumes of time data quickly using whole numbers instead of typing colon-separated time values.
- Observed behavior
- Excel does not offer a built-in number format to interpret whole numbers directly as hours upon entry, treating them as standard integers or dates instead.
Because Excel lacks a built-in setting to convert whole numbers to hours upon entry, you will need to use a VBA macro. Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) before proceeding.
Use a VBA Macro to Convert Numbers to Time
Since there is no native cell format that converts a typed whole number into a real time value, a VBA event macro is the recommended way to automatically intercept your entry and apply the correct hour formatting.
This macro runs automatically when you change a cell's value. It takes the whole number you type, divides it by 24 (since Excel calculates time as fractions of a day), and applies the 'hh:mm' format.
You can restrict this code to a specific range (like Column A) so it doesn't accidentally convert regular numbers in other parts of your sheet.
Right-click the worksheet tab at the bottom of your screen where you want this feature to work, and select 'View Code' from the context menu.
In the code window that appears, paste standard Worksheet_Change VBA code designed to divide the target value by 24 and set the NumberFormat to 'hh:mm'.
Close the VBA editor. Type a whole number like 9 or 13 into the designated cells, and press Enter. It should instantly convert to 9:00 or 13:00.
Use Custom Number Formatting (Visual Display Only)
If you do not want to use VBA and only need the cells to look like times (without actually functioning as time values in calculations), you can use a custom number format.
Try WPS Office for Your Data Formatting Needs
If you are looking for a highly compatible, free alternative to Microsoft Office that supports complex data formatting and VBA macros, WPS Office is an excellent choice. It provides seamless compatibility with Excel files and an intuitive interface.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the on-screen prompts to set up WPS Office on your device quickly.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files to enjoy seamless compatibility and advanced formatting features.
Frequently Asked Questions
Why doesn't Excel recognize '9' as '9:00' by default?
Excel treats whole numbers as days elapsed since January 1, 1900. To Excel, the number 9 represents January 9, 1900, not 9 hours. Time is handled as a fraction of a 24-hour day.
Can I use a formula to convert whole numbers to hours instead of VBA?
Yes. If you enter '9' in cell A1, you can use the formula '=TIME(A1, 0, 0)' in cell B1. This will output a true time value of 9:00 AM, but it requires using a helper column rather than formatting the original cell automatically.
Will the custom format '0\:00' work for time calculations?
No. The custom format only changes how the number is displayed visually. The underlying value is still a whole number integer, which will cause errors if you try to add or subtract times using standard time formulas.
