Bhubon Costa
Microsoft Excel offers a variety of time-saving tools, and understanding them can save you a lot of time. In order to specify precise information, you’ll frequently need to change time format in Excel. In this tutorial, you’ll learn about the features of the Excel time format and how to create an auto-updatable time. You’ll also discover how to use Excel’s time functions to calculate hours, minutes, and seconds.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Change Time Format in Excel
Despite the fact that time is recorded as a regular number, we can use Excel’s extensive time formatting options to show it exactly how we want. Therefore, we will show you 4 suitable ways to change the time format in Excel.
1. Alter the Cell Format to Change Time Format in Excel
As you compose, Microsoft Excel recognizes the time and formats the field accordingly. If you enter 13:30, 1:30 PM, or even 1:30 p in a cell, Excel will recognize it as a time and display 13:30 or 1:30 PM.
Using one of the available Excel time formats as a starting point is the simplest approach to creating a new time format. To do so, follow the instructions given below!
Step 1:
- Select the cells in an Excel sheet where you wish to apply or alter the time format.
- On the Home Tab, press Ctrl + 1 to open the Format Cells dialog box.
Step 2:
- Choose Time from the category list in the Format Cells box, and then select the required time format from the Type list.
- To apply the chosen time format and dismiss the dialog box, click OK.
Step 3:
- Apply all the existing formats to see the changes.
Read more: Dealing with Time Format in Excel
2. Create A Custom Format to Change Time Format in Excel
Regardless of the reality that Excel has a variety of time formats, you may want to create the unique one that better serves the demands of a certain page. To accomplish this, open the Format Cells dialog box, choose Custom from the Category list, then specify the desired time format. Let’s take a look at these two scenarios.
Custom Number Formatting
You may design your customized number formats using the Custom section of the Format Cells Menu.
You must define how to display hours, minutes, and/or seconds to establish custom number formatting for times. As a guide, use the table below.
| Code | Description | Display as |
|---|---|---|
| h | represents Hours lacks a leading zero | 0-13 |
| hh | represents Hours with a leading zero | 00-13 |
| m | represents Minutes lacks a leading zero | 0-49 |
| mm | shows Minutes with a leading zero | 00-49 |
| s | shows Seconds lack with a leading zero | 0-49 |
| ss | shows Seconds with a leading zero | 00-49 |
| AM/PM | display as Periods of the day
(If this field is left blank, the 24-hour time format is applied.) |
AM or PM |
The examples above may be used to just represent hours, minutes, or seconds. You may also merge them to make a whole time.
Custom Formats to Change the Time Format in Excel for 24-Hours Interval
When calculating times, a cell indicating the total of time amounts in Excel may exceed 24 hours. To get Microsoft Excel to correctly show times beyond 24 hours, use one of the custom time formats listed below.
Also, you can customize your own time format as your preference.
| Format | Displays as | Explanation |
|---|---|---|
| [h]:mm | 25:45 | 41 hours and 30 minutes |
| [h]:mm:ss | 25:45:30 | 41 hours, 30 minutes, and 10 seconds |
| [h] “hours”, mm “minutes”, ss “seconds” | 25 hours, 45 minutes, 30 seconds | |
| d h:mm:ss | 1 1:45:30 | 1 day, 1 hour, 45 minutes, and 30 seconds |
| d “day” h:mm:ss | 1 day 1:45:30 | |
| d “day,” h “hours,” m “minutes, and” s “seconds” | 1 day, 1 hour, 45 minutes, and 30 seconds |
Now we will see how these methods work. Let’s follow the steps below.
Step 1:
- Select the cell(s) in an Excel sheet where you wish to apply or alter the time format.
- Press Ctrl+1 or click the Dialog Box Launcher icon next to Number in the Number group on the Home Tab to open the Format Cells dialog.
Step 2:
- Choose Time from the Category list on the Number tab, then input the appropriate time format. For Example, we use [h] “hours”, mm “minutes”, ss “seconds”
The custom time format you’ve created will be in the Type list the next time you need it.
Step 3:
- To apply the chosen time format and dismiss the dialog box, click OK.
Similar Readings:
- How to Custom Format Cells in Excel (17 Examples)
- Use Format Painter in Excel (7 Ways)
- How to Copy Cell Format in Excel (4 Methods)
- Excel Cell Format Formula (4 Effective Methods)
- How to Use Format Painter Shortcut in Excel (5 Ways)
3. Apply the TEXT Function to Change the Time Format in Excel
The TEXT Function is an excellent technique to show a time in text format. The TEXT Function, like the Custom Number Formatting covered above, allows you to show times in formats.
Let’s look at how the TEXT Function formula works first.
=TEXT(value, format_text)
Now we will apply this function to complete our task. We can use both the basic TEXT function and the combination of both the TEXT function and the NOW function. We will discuss both of them in this method.
3.1 Use the Basic TEXT Function
Steps:
- Select a blank cell next to your date, for instance, D5.
- Type this following formula,
=TEXT(B5,C5) - Press Enter to get the result.
3.2 The Combination of the TEXT and the NOW Function
You can combine the TEXT function and the NOW function to display the current time. Let’s see how it is done.
Steps:
- In cell B3, type the following formula,
=TEXT(NOW(),"h:mm:ss AM/PM")- Press Enter to get the desired format.
4. Convert Time as Text to Change the Time Format in Excel
You can convert the time stored as text, back to a time using the TIMEVALUE function. You simply need to format the time cells as general to convert time to number string. This method is discussed below.
Step 1:
- To store a time as text, type an apostrophe (‘) in front of the time.
Step 2:
- Now we will apply the TIMEVALUE In cell C5, apply the function. The formula is,
=TIMEVALUE(B5)
- Press Enter to get the result.
Note: However, as long as the time is stored as the text you’ll be unable to change the formatting like a normal time.
Related Content: How to Format Text Using Excel VBA (12 Methods)
✍ Things to Remember
✎ Excel will display the month instead of minutes if the “m or mm” code is not immediately after the h or hh code or exactly before the ss code.
✎ While applying the TIMEVALUE function, make sure to put an apostrophe (‘) in front of the time, otherwise, it will be not stored as Text.
Conclusion
To conclude, I hope this article has given you some useful information about how to change the Time format in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.
We, The Exceldemy Team, are always responsive to your queries.
Stay with us & keep learning.
Related Articles
I have looked everywhere for a solution but have not found it.
i have a machine that makes timestamps that look like this: 132145
(1:21:45 pm)
i collect multiple lines of these timestamps and am trying to convert them like this:
132145 to 13:21:45
131321 13:13:21
132135 13:21:35
what am i missing in excel?
Hello, AUSTIN!
Thanks for sharing your problem with us!
To convert values into timestamps, follow the instructions below.
1. select the cell and put the formula into that cell.
2. Press Enter.
=(INT(B3/10000)&":"&INT(MOD(B3,10000)/100)&":"&MOD(B3,100))+03. This will convert the values into time values.
4. Now, go to Home tab by selecting the resulted cell and click on Number Format drop-down menu under Number group.
5. Drag the Fill Handle icon down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.
6. And, that’s it! But there is an issue. as 13 represent 1 in time, so 13 will replaced by 1.
Hope this will help you!
If not, can you please send me your excel file via email? ([email protected]).
Good Luck!
Best Regards
Sabrina Ayon
Author, ExcelDemy.