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 (ALWAYS)
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, ...)
For instance, in the following picture, we have the value 0.5 in a cell. If we change the format number to the Time format, this time we have 12 hours. This is logic because 12 hours is the middle of the day, so 0.5 day 😉
What about using Integer?
Now, if you have integer numbers in your cells, and you apply the Time format, all the cells display 00:00:00 (WHY🤔)
It's not a mistake. Excel understands these values are days and not hours. You can see that if you change the format of the cells in Date and Time.
How to Convert an Integer Number in Time
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, you have the correct result 😍😎
If the content of your cell is a seconde and not hour, then the formula is
=8/(24*60*60)
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 (option divide)
