Definition
The SECOND Formula in Excel is one of the numerous time functions available in Excel. We use this to get the seconds component from a given time.
Purpose
The purpose of the SECOND Formula in Excel is to extract and return the seconds from any time value. This becomes highly beneficial when you need to perform calculations or analysis on time data, and you need to isolate or utilize the seconds component specifically.
Syntax
The syntax of the SECOND formula is quite simple, as it only requires a single argument:
SECOND(serial_number)
Parameters
The SECOND formula takes only one parameter:
- serial_number – This is the time from which you want to extract the seconds. The time can be supplied as a text string (like “12:45:30 PM”), a decimal number (like 0.5, which represents half a day, or 12:00 noon), or a cell reference to a cell containing a time.
Returns
The SECOND formula returns an integer between 0 (inclusive) and 59 (inclusive), representing the seconds of the provided time.
Usage notes
When using the SECOND formula, keep in mind that it only considers the time portion of a date-time value. Also, if you enter a time without a date, Excel treats it as a date-time value where the date part is the current date.
Availability
You can use the SECOND formula in all versions of Excel: Excel 2013, Excel 2016, Excel 2019, Excel 2021, and Office 365.
Example #1
Let’s start with a simple example:
=SECOND("9:30:45 AM")
In this example, the formula returns 45, which is the seconds component of the provided time.
Example #2
You can also use cell references:
=SECOND(A2)
Suppose cell A2 has a time value of “6:30:15 PM”. Here, the formula will return 15.
Example #3
The SECOND formula only looks at the time part of a date-time value:
=SECOND("11/22/2023 2:45:35 PM")
Despite including a date, the formula only considers the time and returns 35.
Example #4
If you provide a time without a date, Excel assumes it to be the time on the current date:
=SECOND("12:00:00")
No matter the current date, the formula will return 0 since the seconds component is 0.
Example #5
The SECOND formula can be part of a larger formula. Consider this formula to calculate the total number of seconds in a time:
=HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)
If A2 contains “2:30:45 PM”, the formula will return 52245 seconds.
Tips and tricks
Use the SECOND formula in conjunction with other date and time formulas like HOUR, MINUTE, NOW, etc., to perform advanced calculations.
Limitations
The SECOND formula cannot handle negative time values or time values beyond 24 hours.
Common errors and solutions
One common error is supplying a non-time value. Ensure that your input value is a valid time.
Best Practices
To avoid common errors, make sure the time you supply to the SECOND formula is in a recognized Excel time format.
List of Related functions
The following functions are often used with the SECOND Formula in Excel:
Frequently Used with the formulas
The SECOND Formula in Excel is commonly used with date and time formulas for data analysis.
Frequently Asked Questions
Q. Can the SECOND formula handle time values beyond 24 hours?
No, the SECOND Formula in Excel can’t handle such values.
Q. How does the SECOND formula treat date-time values?
It only considers the time part of the date-time values.
Q. Can the SECOND formula return a negative value?
No, the SECOND formula always returns a value between 0 and 59.
Q. Can the SECOND formula handle non-time values?
No, if you supply a non-time value, you’ll get an error.
Q. Can the SECOND formula be used with other date and time functions?
Yes, it’s commonly used with HOUR, MINUTE, NOW, TIME, etc.
Learn to extract the seconds component from a given time using the SECOND formula in Excel. Get the hang of the usage, limitations, and best practices with practical examples.
Visit our YouTube channel to learn step-by-step video tutorials