The SECOND function is a simple date function that returns the number of seconds from a given time. If you are working with times but only care about the seconds, the SECOND function lets you eliminate everything else.
Remember that dates are just saved as numbers by your spreadsheet with date formatting applied.
This function is rarely used on its own. Typically, it is nested inside other functions, such as TIME.
Contents
Purpose
The SECOND function returns the number of seconds from any given unit of time.
Syntax
=SECOND(time)
Common Errors
#VALUE! – The value passed to the function was not a valid time.
#ERROR! – Formula parse error. The input to the function could not be used, such as a time with no quotes. – 5pm
should be "5pm"
.
#NUM! – The result was not a number, or the number is too large to resolve to a time.
The output is an unexpected format, such as 12:00 AM. – This means the formatting is incorrect, not the underlying number.
TIMEDIF – Google Sheets Add-On
Calculate any duration in:
- Years
- Months
- Weeks
- Days
- Hours
- Minutes
- Seconds
- Milliseconds
Examples
Example 1 – Stand-Alone SECOND Functions
Following are a few simple examples.
Formula | Description | Result |
=SECOND("2:20:20 pm") | The number after the second colon is the seconds. | 20 |
=SECOND("2:20:20.501") | If fractions of a second are used, the seconds are rounded to the nearest whole number. | 21 |
=SECOND("2:20 pm") | No seconds are specified if a colon separates only two numbers. | 0 |
=SECOND("11/25/22 2:20:20") | Any date value is ignored. | 20 |
Example 2 – Separating the Values of a Date and Time
Different date values examined using various functions.
Time value | Tickmark | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | Formatted as number |
---|---|---|---|---|---|---|---|---|
7/30/2016 | 1 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
7-30-2016 | 2 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
6/1/2017 2:32 PM | 3 | 2017 | 6 | 1 | 14 | 32 | 0 | 42,887.61 |
July 31, 2016 | 4 | 2016 | 7 | 31 | 0 | 0 | 0 | 42,582.00 |
12:30 PM | 5 | 1899 | 12 | 30 | 12 | 30 | 0 | 0.52 |
4/11/2017 11:24:14 | 6 | 2017 | 4 | 11 | 11 | 24 | 14 | 42,836.48 |
4/11/2017 | 7 | 2017 | 4 | 11 | 0 | 0 | 0 | 42,836.00 |
8 | 1899 | 12 | 30 | 0 | 0 | 0 | ||
1 | 9 | 1899 | 12 | 31 | 0 | 0 | 0 | 1.00 |
0.25 | 10 | 1899 | 12 | 30 | 6 | 0 | 0 | 0.25 |
43000 | 11 | 2017 | 9 | 22 | 0 | 0 | 0 | 43,000.00 |
- 1 A random date typed in using slashes.
- 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
- 3 Typed a random date in and added a time. Now the function extracts the hours and minutes.
- 4 Typed in a date but spelled out. Writing out the date also works, but it won’t work if you write the day as “31st” instead of 31.
- 5 If you only type in a time, the “zero” date is 12/30/1899.
- 6 Entered with the NOW function. The output will constantly change in the linked Google Sheet.
- 7 Entered with the TODAY function. The output will change daily in the linked Google Sheet.
- 8 A blank cell is December 30, 1899. Similar to tickmark 5.
- 9 Typing a 1 will increment the value in number 10 above by 1 day. In other words, “1=12/31/1899”. It is just a matter of how it is displayed, which can be controlled by going to the Format menu and choosing Number.
- 10 Just a decimal with no whole number increments the value by hours, minutes, and seconds instead of days.
- 11 To get near the current date, you need to start with 43,000 days away from 12/30/1899.
Live Examples in Sheets
Go to this spreadsheet for the examples of the time function shown above that you can study and use anywhere you want.