OmniDocHow to set the date and time format in Excel tables
Explain how to change the display format of date and time in Excel, including preset formats, custom format codes, and how to handle dates when they are saved as text, and give methods to check the results.
Key points of this article
- Dates are essentially numbers in Excel. Changing the format only changes the display mode, not the actual value, so calculations and sorting are not affected.
- First confirm that the cell is a real date, not left-aligned text with a green triangle in the upper left corner. The text needs to be converted first and then formatted.
- Use the "Format Cells" dialog box to select a date or time category. Commonly used presets can meet most needs, and custom codes are used for special styles.
- In the custom code, yyyy, mm, and dd represent the year, month, and day, h, mm, and ss represent the hour, minute, and second, and mm represents the minute in the time period.
- After setting up, spot-check the first and last rows, use formulas to check the values, and confirm that sorting and filtering are still in date order.
Many people enter "2024/3/5" in Excel, but what is displayed is "2024-03-05", or the imported data all turns into a series of numbers. Most of these problems arise in date format. This article is written for readers who often organize reports, attendance sheets, order sheets, and project schedules to explain how to change the date and time to what they need.
After reading, you should be able to determine whether a cell is a real date, know which method to use to change the format, and how to check the result after making the change. The operations in the article are based on the general functions of Excel. The interface positions of different versions may be slightly different, but the setting logic is the same.
🎯 How to set the date format? Which method of changing the format or converting the text is suitable for you? One sentence conclusion
Let me start with the conclusion: If the cell already contains a real date, just change the display format; if it looks like a date but is actually text, you need to convert it to a date first and then set the format. The two situations are handled in different ways, and when they are mixed together, they often become more messy.
| your situation | Recommended practices | reason |
|---|---|---|
| Align cells to the right and automatically display them as you want after input | Directly select the area and set the date or time format | It is a numerical value itself, and changing the format will take effect. |
| The cells are aligned to the left and have a green triangle in the upper left corner | Convert to date first, then format | It is text, and changing the format directly usually does not work. |
| Copied from a web page or PDF and displayed as a series of numbers, such as 45000 | Set according to date format and check whether the number corresponds to the date | This is a date serial number. It will be restored to date after being set to date format. |
| Requires a special style like "March 5, 2024" | Use custom format codes | When the preset format cannot be satisfied, the code can precisely control the display |
| Just display the text results next to it without changing the original data | Generate text using the TEXT function | The original cells keep dates, calculations and sorting unaffected |
Before you start: Confirm these points first
First, confirm whether the data is numerical or text. Dates are numbers in Excel. Changing the format only changes the display method. If the cell is text, the appearance may not change after changing the format because the content itself is not changed into a date. You can enter the formula =ISNUMBER(A1) in a blank cell. The result is TRUE indicating that A1 is a numeric value, and FALSE indicating that it is text.
Second, confirm whether the order of dates is correct. The same is "03/05", some tables understand it by month/day, and some understand it by day/month. This depends on the system locale at the time of entry. When the data sources are different, you should first check a few obvious records, such as dates with months greater than 12, to determine whether the order is reversed.
Third, distinguish between formatting and conditional formatting. This article talks about cell display format, that is, what the date looks like. If you want to "automatically mark the date seven days before expiration in red", that is a conditional formatting rule, which belongs to another type of operation and cannot be replaced by the method here.
Fourth, consider whether the data comes from a PDF. If the table is from a PDF, copying it directly may lose the structure or break up the dates. The PDF editor of the OmniDoc desktop client can perform OCR recognition on scanned documents and export the PDF to Excel. After exporting, you still need to check the date column item by item. Both OCR and export results may have errors, and important data requires manual verification.
Fifth, confirm whether you need to retain the original value. Changing the format will not change the data, but if you use the TEXT function or column sorting function to convert the date to text, the original value may be overwritten. Try to make a copy of the worksheet before starting.
Steps to set date and time format in Excel
- Select the cell or entire column that needs to be set. Click the column label or drag the selected area to avoid changing only a few rows, resulting in inconsistent formats in the same column.
- Open the cell format window. Right-click on the selected area and select the entrance to format cells, or use the shortcut key Ctrl+1, which is a universal quick entrance.
- Select the date or time category in the category list. The date is used for year, month and day, and the time is used for hours, minutes and seconds. If both are needed, you can set the date first and then supplement the time with a custom format.
- Select a similar style from the preset types. There are usually many common writing methods in the list, such as 2024/3/5, March 5, 2024, March 5, 2024, etc. Choose the one that is close to the demand first to reduce subsequent adjustments.
- When special styles are needed, use custom categories instead. Enter the format code in the type input box, for example,
yyyy-mm-dddisplays as 2024-03-05,yyyy 年 m 月 d 日displays as March 5, 2024. - Use h, mm, ss when setting time. For example,
h:mmis displayed as 9:30 andhh:mm:ssis displayed as 09:30:00. Note that mm means minutes when placed next to h or ss, and means months when it appears alone in a date. - Use square brackets when counting cumulative duration. For example,
[h]:mmcan display the total duration of more than one day, such as 36:20, which is useful for attendance and timesheets. - Check the effect after confirmation. Click OK, return to the form to check whether the display meets expectations, and then proceed to the next step for verification.
📊 Core comparison
| Mode | Suitable for the scene | Need to pay attention to |
|---|---|---|
| Preset date format | Regular report, as long as the year, month and day | The style is fixed and words cannot be added at will. |
| Preset time format | Sign-in record, meeting period | Cumulative values for more than one day will be redisplayed from zero, using square brackets |
| Custom format code | Special writing, Chinese date, week | An exception is displayed when the code is miswritten and needs to be tested one by one. |
| The TEXT function generates text | Just need to show, no longer calculate | The generated text cannot be sorted directly or the time difference can be calculated. |
| Separate function conversion text | Date of text imported from outside | You need to choose the right date order, otherwise the month and day will be reversed. |
# check item by item after completion
After the setup is complete, don't end with a glance. Here are a few things that can help you find common problems.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Cell type | Check representative cells with =ISNUMBER() | Return FALSE description is still text, need to convert to date first |
| Show whether it is unified or not | Scroll through the entire column to see if any individual rows have different formats | Re-select the uniform setting of the whole column, or fill it up with a format brush. |
| Date order | Look for records with months greater than 12 to see if they are displayed correctly | If the order is reversed, re-enter or convert in the correct order |
| Sorting and filtering | Sort by date column in ascending order to see if the sequence is reasonable | Sort exceptions are usually text, so go back to the first step |
| Calculation result | Subtract two dates to see if the number of days is in line with common sense | Check whether the cell is the real date when the result is abnormal |
| Time accumulation | Sum the data accumulated for more than one day to see if the display is complete. | When the part of the day is not displayed, use [h]:mm instead |
If the amount of data is very large, it is recommended to spot check the records of the first, middle and tail segments, and then use the formula to do statistics on the whole column, which is more reliable than line-by-line inspection.
# frequently asked questions (FAQ)
1. Is the date changed or unchanged? what's going on?
The common reason is that cells are actually text. You can check with =ISNUMBER(), and if you return FALSE, you need to convert the text to a date before formatting.
2. Why is it that the date shown in Excel is not what I want when I enter "3Universe 5"?
This is usually related to the system locale or typing habits, and Excel understands the date order according to local rules. It is suggested that the complete writing method of "2024-03-05" should be used to reduce ambiguity.
3. How to display only the time, not the date?
Set the cell to a time format, such as h:mm. If the original data has a date, the date part is hidden when the time is displayed, but the value is still retained, and the calculation of the time difference is not affected.
4. What if the custom format code is written wrong?
Reopen the custom format input box, delete the error code, replace it with the correct writing one by one, and test on a small amount of data. The custom format only affects the display, and the original data will not be changed.
5. What should I do if the date of the table exported from PDF is not well formatted?
First confirm that the form is fully exported, and then check column by column to see if the date has been taken apart or errors have been identified. OCR results need to be manually checked, especially the numbers and dates, and then be uniformly formatted after confirmation.
# Summary
The key to setting the date and time format of Excel is to distinguish between numerical value and text, and then choose the appropriate display mode. The preset format can meet most requirements, and the custom code is suitable for special styles. After the setting is completed, do a round of checking with the formula and sorting, you can avoid future calculation and statistical errors.
