The TIME function in Excel

The TIME function in Excel
Last Updated on 05/05/2024
Reading time: 2 minutes

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:

  1. The Hours

    It must be an Integer number representing the value for the hours

  2. The Minutes

    A number that represents your minutes or leaves this argument empty

  3. 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 😉

Same value of 0.5 returns 12 hours

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 😱😱😱

Whole number in Time
  • 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)
Whole number to Date and Time format

TIME function to transform your integer value into a Time value

Examples of the TIME function of Excel
  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

The TIME function in Excel

Reading time: 2 minutes
Last Updated on 05/05/2024

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:

  1. The Hours

    It must be an Integer number representing the value for the hours

  2. The Minutes

    A number that represents your minutes or leaves this argument empty

  3. 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 😉

Same value of 0.5 returns 12 hours

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 😱😱😱

Whole number in Time
  • 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)
Whole number to Date and Time format

TIME function to transform your integer value into a Time value

Examples of the TIME function of Excel
  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *