What is the Excel SECOND Function?
The Excel SECOND function returns the seconds of a time value, an integer number between 0-59.
Use the MINUTE function to extract a minute number and use the HOUR function to extract a hour number
SECOND Syntax
SECOND(serial_number)
serial_number, required, a valid Excel time that contains the seconds you want to find.
Usage Note:
- Times can be supplied as text within quotation marks “6:45 PM”, as decimal numbers 0.78125, which represents 6:45 PM, or as results of other formulas or functions, TIMEVALUE(“6:45 PM”).
- Time values are a portion of a date value and represented by a decimal number, 12:00 PM is represented as 0.5 because it is half of a day.
- For more information, please read “How does Excel Store Dates and Times“
How to Use SECOND Function in Excel
For example, there are 7 data as shown below. What is the SECOND function result if the serial_number argument uses data in column A from the 2nd to 8th row.
SECOND Function #1
=SECOND(A2)
Cell A2 contains a text “3:15:45 AM” with HH:MI:SS AM/PM format, by default Excel displays text with the left aligned. Although in the form of a text, the SECOND function will convert it to the serial_number and the SECOND function returns 45.
SECOND Function #2
=SECOND(A3)
Cell A3 contains a text too “16:20:21” but with 24-hour format HH24:MI:SS, like SECOND function #1, Excel will convert the text to a serial_number and returns 21.
SECOND Function #3
=SECOND(A4)
Cell A4 contains a decimal number, why decimal numbers can be used as SECOND function arguments? Excel stores DATE in integer number and TIME in decimal number.
For further explanation, please read the following article:
SECOND Function #4
=SECOND(A5)
The time value in cell A5 is returned from a TIMEVALUE function. SECOND function #4 and SECOND function #3 return the same results, why? Because of Excel stores 5:05:05 as decimal number 0.21244212962963.
SECOND Function #5
=SECOND(A6)
Cell A6 contains DATE and TIME in one place. Data for serial_number arguments is not only a TIME, but it can also be a DATE and a TIME. As a result, the SECOND function still only takes the seconds of a time value.
SECOND Function #6
=SECOND(A7)
Cell A7 contains text for time with the HH24:MI:SS format, but there is a strange one with the data entered. There should be no 70th seconds, but why does the SECOND function return an integer number?
If there is a time with second value more than 60, then Excel will add the minute value and display the remainder in seconds. Excel considers TIME 23:15:70 as 23:16:10. The result is the SECOND function return 10.
SECOND Function #7
=SECOND(A8)
Cell A8 contains unrelated data as well as cell A7; there should be no 70th second, why the SECOND function returns a number for data in cell A7, but return a #VALUE! error for data in cell A8?
As explained earlier, if there are more than 60 seconds, Excel will add the minute value, if the minute value has reached 60, then Excel will add the hour value. The problem is the hour value has reached its maximum value, which is 24, so the SECOND function returns a #VALUE! error.
The complete results of the SECOND function in column B can be seen in the picture below.
