Extracting Parts of a Time in Excel Formula

Sometimes, we may need to extract only the hour, minute, or second from a date and time value in Excel. For example, we may want to calculate the average arrival time of employees, or the duration of a phone call, or the frequency of a certain event. In this article, we will learn how to use different Excel formulas to extract parts of a time from a date and time value.

Excel handles dates and times using a system in which dates are serial numbers and times are fractional values. For example, June 1, 2020 12:00 PM is represented in Excel as the number 43989.5, where 43989 is the date (June 1, 2020) and 0.5 is the time (12:00 PM). In other words, the time value in a “datetime” is the decimal part of the number.

To extract parts of a time from a date and time value, we can use various Excel functions that can manipulate the decimal part of the number, such as MOD, INT, TRUNC, TIME, HOUR, MINUTE, and SECOND. We will explain how each of these functions works and how to use them in the next section.

Procedures

Using MOD Function

The MOD function returns the remainder from division. The syntax of the MOD function is:

=MOD(number, divisor)

If we use MOD with a divisor of 1, the result will be the decimal part of the number, which is the time value. For example, if we have a date and time value in cell A1, we can use the following formula to extract the time value:

=MOD(A1, 1)

This formula will return a decimal number between 0 and 1, which represents the time value. To display the result as a time format, we need to apply a suitable number format to the cell, such as hh:mm:ss.

Using INT or TRUNC Function

The INT and TRUNC functions both return the integer part of a number, discarding the decimal part. The syntax of the INT and TRUNC functions are:

=INT(number) =TRUNC(number, [num_digits])

The difference between INT and TRUNC is that INT always rounds down the number to the nearest integer, while TRUNC can truncate the number to a specified number of decimal places. For example, INT(3.9) returns 3, while TRUNC(3.9) returns 3.9. However, if we omit the optional argument num_digits, TRUNC behaves the same as INT.

To extract the time value from a date and time value, we can use either INT or TRUNC to remove the date value, and then subtract the result from the original value. For example, if we have a date and time value in cell A1, we can use the following formula to extract the time value:

=A1-INT(A1)

or

=A1-TRUNC(A1)

Both formulas will return the same result as the MOD function, which is the decimal part of the number representing the time value. We also need to apply a suitable number format to the cell to display the result as a time format.

Using TIME, HOUR, MINUTE, and SECOND Functions

The TIME function creates a valid time value from separate hour, minute, and second values. The syntax of the TIME function is:

=TIME(hour, minute, second)

The HOUR, MINUTE, and SECOND functions extract the hour, minute, and second values from a time value, respectively. The syntax of these functions are:

=HOUR(time) =MINUTE(time) =SECOND(time)

To extract the time value from a date and time value, we can use the TIME function with the HOUR, MINUTE, and SECOND functions as arguments. For example, if we have a date and time value in cell A1, we can use the following formula to extract the time value:

=TIME(HOUR(A1), MINUTE(A1), SECOND(A1))

This formula will return a valid time value that matches the time value in cell A1. We can also apply a suitable number format to the cell to display the result as a time format.

Comprehensive Explanation

To illustrate how to use the different formulas to extract parts of a time from a date and time value, let us consider the following scenario:

We have a list of employees’ arrival times on a certain date in column A. We want to extract only the hour value from each arrival time and calculate the average hour of arrival for all employees.

We can use any of the formulas explained above to extract the hour value from each arrival time. For example, we can use the MOD function in column B as follows:

=MOD(A2, 1)

This will return the time value as a decimal number. To display the result as a time format, we can apply the custom number format [h]:mm:ss to column B.

To extract only the hour value from the time value, we can use the HOUR function in column C as follows:

=HOUR(B2)

This will return the hour value as an integer between 0 and 23. We can apply the general number format to column C.

To calculate the average hour of arrival for all employees, we can use the AVERAGE function in cell C9 as follows:

=AVERAGE(C2:C7)

This will return the average of the hour values in column C, which is 9.5. We can apply the general number format to cell C9.

The following table shows the result of applying the formulas to the data:

Other Approaches

Besides the formulas explained above, there are some other approaches to extract parts of a time from a date and time value in Excel. Some of them are:

• Using the TEXT function to convert a date and time value to a text value in a specified format. For example, we can use the following formula to extract the hour value from a date and time value in cell A1:

=TEXT(A1, “h”)

This will return the hour value as a text value. We can also use other format codes to extract other parts of a time, such as “m” for minute, “s” for second, “AM/PM” for AM or PM indicator, etc.

• Using the DATEVALUE and TIMEVALUE functions to convert a date and time value to a date serial number and a time serial number, respectively. For example, we can use the following formula to extract the time value from a date and time value in cell A1:

=TIMEVALUE(A1)

This will return the time value as a decimal number. We can also use the HOUR, MINUTE, and SECOND functions to extract the hour, minute, and second values from the time value.

• Using the Data > Text to Columns feature to split a date and time value into separate columns based on a delimiter, such as a space or a slash. For example, we can select the range of cells that contain date and time values, and then go to Data > Text to Columns. In the wizard, we can choose the Delimited option, and then select the Space delimiter. This will split the date and time values into two columns, one for date and one for time. We can then use the HOUR, MINUTE, and SECOND functions to extract the hour, minute, and second values from the time column.