In Excel, working with the time is not difficult but you need to know a few rules to avoid big mistakes. In this article you will learn these rules and how to manage time formats.
Difference between date and time in Excel
In Excel, hours are always a fraction of a day. So it is necessarily decimal numbers.
This rule is fundamental to avoid mistakes in calculation and display.
- Dates are whole numbers (like 1, 2, 3, ....)
- Hours are decimal numbers (like 0.5, 0.33333, ...)
A whole number will never be understood as hours or minutes in a worksheet.
Visualize the differences
This document displays the same value, 8, in 3 different formats.
- First cell, 8 in the General format
- Second cell, 8 in the Time format
- Third cell, 8 in the Date format
In Excel, 8 is 8 days. So it's normal that when you convert to Time, the result is 00:00:00 for midnight because it only shows the time and not the date.
When the format Date & Time is used, the value 8 is understood as 08/01/1900, which is 8 days after 01 January 1900.
Convert a whole number to a fraction
If you have to convert a whole number to Time, you must divide that number by 24 (the number of hours in a day 😉).
=8/24 = 0,333333
Then, you apply the Time format to the cell and it displays 08:00:00 😍😎
Technique with Paste Special - Divide
DON'T waste your time. There is a technique to divide all your numbers by 24 in 2 steps.
The secret is to use paste special with the option divide. 😍😍😍
Customize hours, minutes and seconds
In the article Date Format, we saw how to customize the date and time using the dialog box Number Format.
To customize a time, you use the following parameters.
- h for the hour
- m for the minute
- s for the second
You can also display the tenth, hundredth and thousandth. But for that, you have to add 0.
Hours over 24 hours
By default, if a time calculation exceed 23:59:59, Excel will display in number of days and hours.
To avoid this, go to this article to know the technique to display hours over 24 hours