In Excel, working with the time is not difficult but you need to know few rules to avoid big mistakes. In this article you will see these rules and how to manage the time format.
Difference between date and time in Excel
In Excel, hours are always a fraction of a day. So it's decimal numbers
This rule is fundamental to avoid mistake in the calculation and display
- Dates are whole number
- Hours are decimal number
Visualize the differences
- First cell, 8 with the General format
- Second cell, 8 with the Time format
- Third cell, 8 with the Date format
For Excel, 8 is 8 days. So it's normal that when you convert in Time, the result is 00:00:00 for midnight
Now, when the format is Date & Time, the value 8 is understand as the 08/01/1900
Convert your whole number in fraction
=8/24 = 0,333333
Then, you apply the Time format an the cell displays 08:00:00 😍😎
There is also an easiest way to convert a whole set of whole number in fraction with paste special, option operation divide.
Customize Hours, Minute and second
In the article Date Format, we have seen how to customize the date and time with the dialog box Number Format.
To customize a time, you can use the following parameters.
- h for the hour
- m for the minute
- s for the second
It is not possible to have a custom format just for the minutes.
The reason it's because m is by default the parameter for the month. So you can create a custom time format with, hour & minute or minute & second, but never minute alone.
Hour greater than 24H
If you want to display hour after 24H, you have to write the code between bracket
If you don't do that, Excel will always display a number of hours between 0 and 23 and the rest is convert in number of days.
Here 3.25 is 3 days and 6 hours or 78 hours. It depends of the format of the h.
All the Time format in one document
In the following document, you have a sum up of all the combinations of Time format.