The TIME function in Excel is used to create a time value from individual hour, minute, and second components. It converts these components into a decimal form that Excel recognizes as a time of day. The syntax of the function is straightforward:
- The Hours
It must be an Integer number representing the value for the hours
- The Minutes
A number that represents your minutes or leaves this argument empty
- Seconds
A number that represents your seconds or leaves this argument empty
=TIME(Hours,Minutes,Seconds)
Times is necessarily a decimal value
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, we have 12 hours this time. This is logical because 12 hours is the middle of the day, so 0.5 day 😉
Most people use integer numbers to write their time values.
It's a fact that writing a time in Excel is not easy because you must use the symbol colon ( : ), like 12:00:00. Many people prefer to write the value 12 (an integer number). But when you apply a Time format to an integer number, the only result you have it's 00:00:00 😱😱😱
- 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.
- 8 has become the 08 January 1900, 8 days after the very first day known by Excel (01/01/1900)
TIME function to transform your integer value into a Time value
- It doesn't matter if some components are missing, like minute or second for instance
- When you convert the result in General format, you can see that all the results are decimal
