Learn how to manage Time Format in Excel to avoid mistakes. Since manipulating Date and Time can be tricky, calculations might be inaccurate.
How to write a Time in a cell?
In Excel, when you want to write a time in a cell, you MUST split the hours, minutes, and seconds with a colon (:). Automatically, Excel will apply the Time format to your values.
But in most cases, it doesn't work. 🤔
But, usually, people don't write time with the colon sign. Most people just write the value of the hours with an integer number. Then, from the ribbon, they try to display the value in Time format and .... it doesn't work. In this situation, Excel will display 00:00:00 for each cell.
- It's not a mistake!!!!
- You have integer numbers in Column A.
- Excel understands these values are days and not hours (hours are decimals)
- You can see that if you change the format of the cells in Date and Time.
- 8 has become the 08 January 1900; 8 days after the very first day known by Excel (01/01/1900)
In Excel, hours are always a fraction of a day.
- 0 it's Midnight
- 0.5, it's half-day, so it's 12:00:00
- 0.33333, it's the third of a day, so 08:00:00
- 0.99999, it's 23:59:59
How to Convert an Integer Number in Time
If you are in this situation, you MUST convert your values. And there are 2 ways to do that
- Using the TIME function of Excel (easiest solution)
- By doing formula
- Divide hours by 24 =hours/24
- Divide minutes by 24*60 =minutes/(24*60)
- And for the seconds =seconds/(24*60*60)
Custom Time format in Excel
Once you've entered the correct time values in your cells, as Excel requires, you can customize the format using the three components of time.
- h for hour
- m for minute
- s for second
You can combine each component to display you time in many different ways
- hh:mm:ss => 08:35:14
- hh:mm => 08:35
- mm:ss => 35:14 (only minutes and seconds)
- hh:mm AM/PM => 08:35 AM
- [h]:mm => 27:13 (to display over 24 hours)
- But you can't write mm alone (because Excel understands that it's for Months)
Where to write this Code?
- Go to Dropdown list of the number format
- Click on the last option More Number Formats
- In the dialog box, select the Custom Category.
- In the text box, write your code
- Click on OK
