How to Change Time Format in Excel (4 Ways)

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 + to open the Format Cells dialog box.

Alter Cell Format to Change Time Format in Excel

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.

Alter Cell Format to Change Time Format in Excel

Step 3:

  • Apply all the existing formats to see the changes.

Alter Cell Format to Change Time Format in Excel

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.

Create Custom Formats to Change Time Format in Excel

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”

Create Custom Formats to Change Time Format in Excel

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.

Create Custom Formats to Change Time Format in Excel


Similar Readings:


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.

Apply the TEXT Function to Change Time Format in Excel

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.

Apply the TEXT Function


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.

Convert Time as Text

Step 2:

  • Now we will apply the TIMEVALUE In cell C5, apply the function. The formula is,
=TIMEVALUE(B5)

Convert Time as Text

  • Press Enter to get the result.

Convert Time as Text

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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo