Functions HOUR, MINUTE and SECOND to work with time in Excel

Excel defaults to date and time functions: HOUR, MINUTE and SECOND. Consider in detail these three functions in action on specific examples. How, when and where they can be effectively applied, making up various formulas from these functions for working with time.



Examples of using the functions HOUR, MINUTE and SECOND for calculations in Excel

The HOUR function in Excel is designed to determine the hour value from the transmitted time as a parameter and returns data from a range of numeric values from 0 to 23, depending on the format of the temporary record.

The function MINUTE in Excel is used to get the minutes from the transmitted data, which characterizes the time, and returns data from a range of numeric values from 0 to 59.

The SECOND function in Excel is used to get the seconds from data in a time format and returns numeric values from 0 to 59.



Monitoring the daily time clock in Excel using the HOUR function

Example 1. Get the current time, determine how many hours have passed since the beginning of the current day, how many hours left before the beginning of a new day.

Source table:

Example 1.

Let's define the current moment in the Excel time code:

define the current moment.

Calculate the number of hours from the beginning of the day:

number of hours.
  • B2 - the current date and time, expressed in the format Date.

Determine the number of hours before the end of the day:

hours before end.

Argument Description:

  • 24 - the number of hours per day;
  • B3 is the current time in hours, expressed as a numerical value.

Note: The example demonstrates that the result of the work of the HOUR function is a number on which you can perform any arithmetic operation.

Conversion of numbers to time format using the functions HOUR and MINUTE

Example 2. From the application, moments of passing certain events that were recognized by Excel as ordinary numbers were loaded (for example, 13:05 was recognized as the number 1305). It is necessary to convert the obtained values into the time format, select the hours and minutes.

Source data table:

Example 2.

To convert the data, use the function:

TEXT.

=TEXT(B3,"00\:00")

Argument Description:

  • B3 - the value recognized by Excel as a normal number;
  • "00\:00" is the time format.

As a result, we get:

result.

Using the functions HOUR and MINUTE, select the desired values. Similarly, we define the required values for the remaining events:

HOUR and MINUTE.

Example of using the SECOND function in Excel

Example 3. The work day starts at 8:00 am. One worker was systematically late for the previous 10 working days for a few seconds. Determine the total time the employee is late.

Enter the data in the table:

Example 3.

Determine the delay in seconds. Where B3 - data on the time of arrival at work on the first day. Similarly, we define the seconds of delay for the following days:

using the SECOND function.

Determine the total number of seconds of delay:

total number of seconds.

Where C3:C12 is an array of cells containing seconds of late values. We define the integer value of the minutes of delay, knowing that in 1 min = 60 seconds. As a result, we get:

late values.

That is, the total lateness of an employee for 10 days was 263 seconds, which is more than 4 minutes.

Features syntax functions HOUR, MINUTE and SECOND in Excel

The HOUR function has the following syntax entry:

=HOUR(serial_number)

  • serial_number is the only function argument (required) that characterizes time data that contains data about the clock.

Notes:

  1. If a string with text that does not contain time information is passed as an argument to the HOUR function, the error code #VAL! Will be returned.
  2. If the logical data type (TRUE, FALSE) or a reference to an empty cell was passed as an argument to the HOUR function, the value 0 will be returned.
  3. There are several permitted data formats that the HOUR function accepts:
  • In Excel time code (range of values from 0 to 2958465), while the integers correspond to days, fractional - hours, minutes and seconds. For example, 43284.5 is the number of days elapsed between the current moment and the starting point of reference in Excel (01/01/1900 is a conditional date). Fractional part 0.5 corresponds to 12 hours (half of the day).
  • In the form of a text string, for example =HOUR(“11:57”). The result of the function - the number 11.
  • In the format of the Date and Time Excel. For example, the function will return the values of the clock if, as an argument, it receives a reference to the cell containing the value “07/03/18 11:24” in the date format.
  • As a result of the function that returns data in a time format. For example, the function =HOUR(TIMEVALUE(“1:34”)) returns the value 1.

The MINUTE function has the following syntax:

=MINUTE(serial_number)

  • serial_number is a required argument describing the value from which the minutes will be calculated.

Notes:

  1. As in the case of the HOUR function, the MINUTE function accepts text and numeric data in the format of Dates and Times.
  2. If the argument of this function is an empty text string (“”) or a string containing text (“some text”), the error #VALUE! Will be returned.
  3. The function supports date format in Excel time code (for example, =MINUTE(0.34) returns the value 9).

The syntax of the SECOND function in Excel is:

=SECOND(serial_number)

  • serial_number is the only argument represented as data from which the seconds will be calculated (required for filling).

Download examples HOUR, MINUTE and SECOND to work with time in Excel

Notes:

  1. The SECOND function works with text and numeric data types representing Date and Time in Excel.
  2. Error #VALUE! will occur in cases where the argument is a text string that does not contain data characterizing the time.
  3. The function also calculates seconds from the number represented in the Excel time code (for example, =SECOND(9,567) returns the value 29).

en ru