The SECOND function is categorized as a DATE/TIME function in Excel. It extracts and returns the seconds component from a time as a value between 0 and 59. For instance, when the SECOND function is used on a timestamp 12:50:45, the SECOND function returns 45.
Syntax
The syntax of the SECOND function is as follows:
=SECOND(serial_number)Arguments:
'serial_number' – This is a required argument where you must enter the serial number for a valid, Excel-recognized time from which you want to extract the seconds component.
Important Characteristics of the SECOND function
- The SECOND function only accepts one argument, which must be an Excel-recognized time value.
- The serial_number argument may be a cell reference, decimal value (serial number representing time), text string representing time, or a time value returned from another function.
- The SECOND function always returns a value between 0 and 59. This is because the function resets to 0 every 60 seconds. For instance, when the duration is 2 minutes, the SECOND function returns 0 even though the total number of seconds in 2 minutes is 120.
- If the time value supplied doesn't have a seconds component, Excel assumes the seconds component as 0, and therefore the SECOND function returns 0 as well.
- When the serial_number argument is invalid, the SECOND function returns a #VALUE! error.
Recommended Reading: Excel Minute Function
Examples
Let's try to see some examples of the SECOND function.
Example 1 – Plain Vanilla Formula for the SECOND Function
Let's get our foot in the door with the plain vanilla formula of the SECOND function. We have a timestamp in cell A2 and we'll extract the seconds component out of it using the following formula:
=SECOND(A2)
The function returns 30, extracting the seconds component from the supplied timestamp 15:25:30. Easy peasy lemon squeezy, right?
Notice that we used a cell reference to a time value in cell A2 with the HH:MM:SS format. However, there are a few variations you can use here in terms of both, the format of the supplied time value as well as the way you supply it.
Example 2 – Other Valid Inputs for the SECOND Function
In the previous example, we used a HH:MM:SS input. However, there are several other timestamp formats that the SECOND function recognizes. Following is a table illustrating those recognized formats:
| Input | Output | Comment |
|---|---|---|
| 3:25:30 PM | 30 | The function works with twelve-hour timestamps as well. |
| 12/25/2001 10:55 | 55 | The function ignores the date and hours/minutes components. You could use a 12-hour timestamp here and the function will still work fine. |
| 35:25:45 | 45 | The function accepts values greater than 24 hours. |
| 0.705 | 12 | This is essentially the same as computing the timestamp when 70.5% of the day has passed. In this case, the timestamp would 16:55:12. |
Just as there are multiple acceptable inputs, there are multiple ways to give inputs to the SECOND function. In the previous example, we referenced cell A2. However, we could have also used the following ways to input the timestamp:
=SECOND("3:25:30 PM") //12hr Time Format
=SECOND("12/25/2001 10:40:55 AM") //Ignores Date and Time
=SECOND("35:25:45") //Accepts values greater than 24 hours
=SECOND(TIME(5,20,35)) //Time function passed as a parameter
=SECOND(0.705) //Accepts decimal inputs
In the first and second sets of formulas, we are passing text strings representing timestamps to the SECOND function. The SECOND function accepts both 12- and 24-hour timestamps, and therefore, both text string representations should work.
In the third formula, we have passed a timestamp where the hour component is in excess of 24. In all such cases, Excel by default resets the hour component to 0 after 23 and hence the SECOND function works just fine with all such inputs.
The fourth formula uses the output from another function (i.e., the TIME function) and returns the seconds based on that.
The final formula is just the decimal value 0.705 directly passed into the SECOND function. In this case, Excel calculates the time from the decimal value (0.705) by multiplying it by the total number of hours in a day (24), which comes out as 16.92.
Out of the above result 16.92, the part before the decimal point is considered to be the hour component. So in our case 16 is the hour component. The part after the decimal (0.92) is again multiplied by the total number of minutes in an hour (60), which comes out as 55.2.
Out of 55.2, the part before the decimal point is considered to be the minute component. So here 55 is the minute component. The part after the decimal (0.2) is again multiplied by the total number of seconds in an hour (60), which comes out as 12.
The final value calculated by excel is 16 hours, 55 minutes, and 12 seconds (16:55:12), which is supplied to the SECOND function.
Therefore, in this case, the SECOND function returns 12.
Example 3 – Nesting the SECOND function Inside the TIME Function
Just like the SECOND function accepts the TIME function's output, the reverse is true as well. If you have a column populated with timestamps, you can extract the seconds component using the SECOND function and relay it to the TIME function to create a new timestamp. You'll need to use the following formula:
=TIME(2, 5, SECOND(A2))
The first two arguments of the TIME function represent the hour and minute components, respectively. The third argument represents the seconds component, and therefore, we've nested the SECOND function in the third argument. The SECOND function will extract the seconds component, relay it to the TIME function, and voila!
If the output doesn't show the seconds component, make sure you've formatted the cell appropriately to display seconds. To custom format, a cell, right-click > Format Cells and choose Custom. Enter the custom format as hh:mm:ss and the cell should now display the seconds component.
That brings us to the end of this super-simple Excel function. Don't agonize, though. We have a ton of functions tucked away in our backpack and we're crafting our next tutorial for you as you read this. Meanwhile, be sure to practice these formulas on your own worksheet.
When you feel confident about the SECOND function, come back to us and we'll make sure we have the next function ready for you to learn. Until then—stay curious!
About Supriya