How to Display 6:00 AM or 6:00 PM Correctly in Excel
Question details
The user wants to format and display specific times like 6:00 AM or 6:00 PM in Excel but gets incorrect date and time values when entering whole numbers.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering hour values into cells and attempting to format them as specific times using the number formatting tools.
- Observed behavior
- Excel interprets a whole number like 6 as six whole days rather than six hours, resulting in an incorrect date/time display (like January 6, 1900) when a time format is applied.
Before adjusting the formats, identify whether your existing spreadsheet data contains whole numbers (representing hours) or if you are entering new time data from scratch, as the method will differ.
Correctly Enter Times with Colons
The simplest way to display 6:00 AM or PM is to input the time directly using the correct colon syntax so Excel automatically recognizes it as a time value.
Excel stores time as a fraction of a day (24 hours). Entering the number '6' tells Excel you mean 6 full days. To tell Excel you are entering hours and minutes, you must include a colon.
Click on the cell where you want to input the time value.
Type '6:00' or '6:00 AM' (include the colon) instead of just typing the number '6'.
Press Enter. Excel will automatically recognize the colon and apply the appropriate custom time format.
Convert Existing Numeric Values to Time Format
Use a basic division formula to convert whole numbers already entered in your spreadsheet into correctly formatted time fractions.
Easily Manage Time Formats with WPS Spreadsheet
WPS Spreadsheet handles dates and times exactly like Microsoft Excel, ensuring full formula and format compatibility. You can quickly input, convert, and custom-format time values in this lightweight, free alternative.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your time data.
- 2. Access cell formatting: Right-click the cell containing your time value and select 'Format Cells' from the context menu.
- 3. Apply a custom format: Under the 'Number' tab, click 'Custom' and choose or type 'h:mm AM/PM'.
- 4. Save changes: Click OK to immediately apply the correct 12-hour time display to your data.
Frequently Asked Questions
Why does Excel show 1/6/1900 when I type 6 and format it as time?
Excel uses a sequential serial number system for dates starting from January 1, 1900. Typing '6' represents exactly 6 days after this start date. When a date/time format is applied, it displays January 6, 1900.
How do I change the time format from 24-hour to 12-hour AM/PM?
Right-click the cell and select Format Cells. Navigate to the Number tab, select Custom, and enter 'h:mm AM/PM' in the Type box to force a 12-hour display.
Can I use formulas to extract just the hour from a time cell?
Yes, you can use the =HOUR(A1) formula. If cell A1 contains 6:00 AM (stored as 0.25), the formula will return the whole number 6.
