Loaded with multiple timesaving features and letting you understand its importance, Microsoft Excel is a great tool. As we know, Excel can handle numbers and text both likewise; we often tend to have data containing time. Sometimes, we have to change time formats according to the dataset requirements. Let’s understand how to format time in Excel. Here we will get to know some worthy enough time formats used in Excel to update the sheet.
Understanding the Time Format Code
We know the Excel time format code is hh:mm:ss, let’s understand this code further in detail.
hh: this time code shows the hour in double-digit value.
mm: this code shows the minutes part of the time in a double-digit value.
ss: this code shows the second part of the time.
Apart from this standard coding, we have some additional detail as well:
h: this code shows hours without a leading zero, such as 0-23
m: This code shows minutes without a leading zero, such as 0-59
s: this code shows the seconds part without a leading zero, such as 0-59
AM/PM: this code shows the periods of the day, when 24, the hour time format is used, such as AM or PM
In case, when you don’t need to see the “seconds” part, you can apply only the “time and minute” part of the code.
You can even personalize the time format. For instance, “0.689” is equal to the time of “04:32:10 PM.”
You can even make changes such as “04 Hours, 32 Minutes, 10 Seconds”.
After that, we will get the following results.
For this, you have to add the following custom time code.
hh “hours”, mm “Minutes”, ss “Seconds” AM/PM
This is how to format time in Excel.
Change Time Format in Excel
Though we use typical time formats, however, Excel lets you use additional formats to show how exactly you want your sheet to appear. Here we have some useful hacks that let you know how to format time in Excel:
Change the Cell Format to Alter Time Format in Excel
Excel has preset fields for time and formats. Suppose you add 13:30, 1:30 PM, or 1:30 p in a cell, you don’t need to explain it because Excel will understand that it is time. We will use the most simple time format to start with.
Let’s follow the steps given below:
- Select the cells from the sheet that you need to make changes in the time format.
- Press Ctrl + 1 to open the Format Cells menu bar.
- Now, choose the Time option from the category list given in the Format Cells box and click on the required time format from the Type list.
- For this application, you have to choose the time format and close the dialog box.
- Click Ok.
- You can apply all the formats to see how they look.
Things to Consider
Always remember that time will be stored as decimal values in Excel.
- The time and date both are combined in Excel.
- You have to enclose the time format code of the hour part within the parenthesis when the time value exceeds 24 hours, such as “[hh]:mm:ss.”
- In Excel, if the “m or mm” code is not added right after the h or hh code or right before the s or ss code, it will be considered as a month and not minutes.
Wrapping Up
Hopefully, this post gives you what you need. At least you are now aware of how to format time in Excel, so you can try any of the methods given above. Each method is unique and can provide the desired results. Continue sharing knowledge and keep on exploring Excel. Best of luck!
