Dates function
Excel SECOND function
SECOND returns the seconds part of a time as a number from 0 to 59. It is useful for race and lap times, call durations and timestamps from systems that log to the second. Like HOUR and MINUTE, it reads one part of the clock only.
=SECOND(serial_number)Arguments
serial_number- A time, a date-time, a cell holding one, or a time in quotes such as "0:04:37".
SECOND examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | Runner | Lap 1 | Lap 2 |
| 2 | Kim | 0:05 | 0:05 |
| 3 | Lee | 0:05 | 0:05 |
| 4 | Ana | 0:04 | 0:04 |
Download this sheet as an Excel practice file (.xlsx)
Result: 37
Kim’s first lap was 4 minutes 37 seconds, so SECOND returns 37.
Result: 277
The lap in total seconds: 4 times 60 plus 37 is 277.
=B2*86400gives the same, since a day has 86,400 seconds.Result: 5
Rounds the lap to the nearest whole minute: 37 seconds is 30 or more, so 4 becomes 5.
Result: #NUM!
Lee’s second lap was 14 seconds faster, but C3-B3 is negative, and a negative time is #NUM! for SECOND. Subtract the other way round, B3-C3, or use ABS.
How to use SECOND
- Type times with hours, such as 0:04:37, so Excel reads 4 minutes 37 seconds, not 4 hours 37 minutes.
- Click the result cell and type =SECOND( then click the time.
- Close the bracket and press Enter.
- For total seconds, use =time*86400.
Common SECOND mistakes
Typing 4:37 for a lap time
Excel reads 4:37 as 4 hours 37 minutes, so SECOND returns 0. Type 0:04:37, or format the cells as mm:ss first.
Negative differences
Times cannot be negative in Excel’s default date system, so SECOND, MINUTE and HOUR return #NUM! on one. Subtract the smaller time from the larger, or use ABS.
A total of lap times that wraps round
Summed laps of 65 minutes show as 05:00 in an mm:ss format, because mm only counts up to 59. Format the total as [mm]:ss to show 65:00.
SECOND FAQ
How do I convert minutes and seconds to seconds in Excel?
If the cell holds a time such as 0:04:37, use =A2*86400 for 277 seconds. If it holds text like "4:37", use =TIMEVALUE("0:"&A2)*86400.
How do I show seconds in Excel?
Format the cell with a custom format such as h:mm:ss, mm:ss or [s] for total seconds. Formatting does not change the value.
Why does SECOND return 0?
Usually the time was entered without hours, so 4:37 became 4 hours 37 minutes and 0 seconds. Re-enter it as 0:04:37.
Related functions
Updated