How to use Excel SECOND Function?
This SECOND Function is used to return the Second as a numerical output [0-59] from a input Time
SECOND Function: A Brief
The SECOND function in Excel extracts the seconds part of a given input time and returns a number between 0 and 59.
For example, if the time is 05:45:43 AM, the SECOND function will return 43. You can use this function to pull out the seconds and display them in a separate cell.
| Objective | Value Returned by function |
|---|---|
| Aim to get the Seconds as a numerical value | SECOND Function will return a numeric values of seconds from 0 to 59 from the input time value. |
Function Compatibility:
The SECOND function was introduced in Excel 2000. It is supported in all modern versions of Excel, including Excel 2003, 2007, 2010, 2013, 2016, 2019, and Microsoft 365.
SECOND Function: A Syntax
=SECOND(input_time)- input_value: The input value must be a valid excel time.
Basic Example:
The example below will help you understand how the SECOND function works in Excel.
From the example shown, the SECOND function extracts the seconds part from the given time and returns it as a number.
Example 2: Extracting Seconds from Fractional seconds:
Note: The limitation of the SECOND function is that it rounds fractional seconds to the nearest whole second.
In the example shown, the SECOND function is used to extract seconds from the times listed in column B, which include values in hundredths of a second. During this process, the fractional part is ignored, and the value is rounded off to the nearest second.
| Input Time with Fractional Seconds | Final Round off Seconds Output | Formula used |
|---|---|---|
| 01:50.82 | 51 | =SECOND(B4) |
| 03:12.45 | 12 | =SECOND(B5) |
| 05:33.19 | 33 | =SECOND(B6) |
| 02:45.67 | 46 | =SECOND(B7) |
| 04:22.98 | 23 | =SECOND(B8) |
| 07:05.21 | 5 | =SECOND(B9) |
| 06:48.36 | 48 | =SECOND(B10) |
| 08:10.73 | 11 | =SECOND(B11) |
| 09:59.44 | 59 | =SECOND(B12) |
| 02:33.85 | 34 | =SECOND(B13) |
| 05:12.09 | 12 | =SECOND(B14) |
| 04:50.77 | 51 | =SECOND(B15) |
For the input time “05:12.09”, the SECOND function returns 12 seconds, as the fractional part (.09) is less than 0.50, so it does not round up. However, for the input time “04:50.77”, the SECOND function returns 51 seconds, because the fractional part (.77) is greater than 0.50, so it rounds up to the next second.
Example 3: Extracting Seconds from the Present Time:
You can extract the second value from the current time. You might need to use the =NOW() function to get the current time and then you need to use the =SECOND(NOW()) formula.
| Functions Used | Description |
|---|---|
=NOW() | It is used to get the current time value. |
=SECOND() | It is used to get the seconds value from the given input time. |
=SECOND(NOW()) | Extract the seconds from the current time value. |
Example 5: Using SECONDS Function with TIME Function:
You can use the SECONDS function along with the TIME function to extract the seconds value from the result given by the TIME function.
=SECOND(B4 + TIME(0,1,25))
=SECOND(B4 + TIME(0,1,45))For a given input time in the cell “01:15:40”, the output value in the cell C4 will be shown as 5, because 40s + 25s = 65 seconds, which equals 1 minute and 5 seconds and so this function returns the value 5.
Example 6: Using SECOND Function with other Nested Functions:
| Input Time | Output Results | Formula Used | Description |
|---|---|---|---|
| 13:25:46 | 50 | =SECOND(TIMEVALUE(MID(A1,13,8))) | Extracts the time portion from a text string and gets the seconds. |
| 20-03-2025 17:48 | Under 30 seconds | =TEXT(NOW(),"hh:mm:ss") & " (Seconds: " & SECOND(NOW()) & ")" | This formula combination will display something like: “15:27:45 (Seconds: 45)” |
| 65 | 5 | =SECOND(TIME(0,0,MOD(A1,60))) | If the cell has 65 (seconds), it calculates the leftover seconds after converting to minutes (65 seconds = 1 minute and 5 seconds). |
| 20-03-2025 17:48 | 17:48:21 (Seconds: 21) | =IF(SECOND(NOW()) > 30, "Over 30 seconds", "Under 30 seconds") | This formula combination will dynamically show either “Over 30 seconds” or “Under 30 seconds” based on the current time’s seconds value. |
| The time is 12:45:33 | 33 | =ROUND(SECOND(A1)/10,0) * 10 | This will round the seconds to the nearest 10 |
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
References:
- SECOND Function by Microsoft Support, Retrieved on 23/3/25: Link.
- How To Use Second Function In Excel | How to extract and manipulate Second from time value in Excel by The Data Millennials, Retrieved on 23/3/25: Link.
- mm:ss formatting and conversion to seconds by excel subreddit, Retrieved on 23/3/25: Link.
- How to Convert Time Value into Seconds in Microsoft Excel by Linkedin Pulse, Retrieved on 23/3/25: Link.
