Excel provides various functions to maximize and speed up your productivity. Today we are going to show you how to use the Excel SECOND function. For the session, we are using Excel 2019, you can use your preferred version (at least Excel 2003).
The SECOND function can be used for extracting seconds from different types of time input. Now without further ado, let’s begin.
You are welcome to download the practice workbook from the link below.
Excel SECOND Function
The SECOND Function is categorized under Excel DATE & TIME functions.
You will see various functions and their respective categories in the Function Library. As per our agenda let’s start our journey to know about the SECOND function.
1. Basics of Excel SECOND Function
The SECOND function returns the seconds from a time value.
Returns the second, a number from 0 to 59.
serial_number: The time value to extract the second from. This should be a valid time that is recognized by Excel.
From this Format Cells box, you can see the valid time format from different regions. To open the box, you need to press CTRL + 1.
This function is workable from Excel version Excel 2003.
2. Use of Excel SECOND Function
I. Basic Use: Fetch second from a given time
From the basics of the SECOND function, you have understood that this function will fetch second from time.
To show you examples we have introduced a table that contains employee names and their entry times.
We are going to fetch the second from here.
Our formula will be
For simplicity, we have named time as our parameter.
Write the formula in Excel.
Here we have inserted the time as Cell Reference. D4 contains 10:10:03, so in the second field, we have 03. The function returned 3.
Do the same for the rest of the rows or use the Excel AutoFill feature. Practice makes a man perfect; you know. Haha!
II. Find Difference in seconds
We may need to find the difference in seconds. The SECOND function can be useful there.
Let’s imagine a scenario where we have a few marathoners with their timing and position in a marathon race.
We will find the difference from the winner (1st place holder). The scenario is basic to keep things simple.
All we need to fetch the second from their timing using SECOND. And then operate subtraction.
Obviously, we don’t need to compare one with himself, so we have started the proceedings in the second row.
Using the SECOND function we have extracted the seconds and then subtract respective marathoners from the winner.
We are using the ABS function that provides value irrespective of the sign.
Do the same for the rest of the values.
III. Use in Nested Formula
The SECOND function can be used with many other functions as well. Depending on different circumstances we need to use the nested formula using the SECOND function.
Let’s assume a scenario, where you need to find the exit time of an employee from their entry time and working hour.
We need to use the TIME, HOUR, and MINUTE functions along with the SECOND function. Together these functions create a nested formula.
The MINUTE function extracts the minute component of a time as a number between 0-59.
To know more about the function visit this article about MINUTE.
The HOUR function returns the hour component of a time as a number between 0-23.
To know more about the function visit this article about HOUR.
From the syntax, you might have understood that the MINUTE and HOUR functions are similar to the SECOND function.
The TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components.
TIME(hour, minute, second)
We will insert hour, minute, and second using HOUR, MINUTE, and SECOND respectively.
To complete our task, we are going to use the formula which will be something like this
Here MINUTE and SECOND extract the minute and second value from the time respectively. Simple as you like.
HOUR extracts the hour from the time and then we add the hours to this derived hour number.
Write the formula in Excel.
We have found the time after adding the hours. As per our scenario, only the hour has been changed. Rests remain as it was.
Do the same for the rest of the rows.
3. Quick Notes
The SECOND function can be used on different occasions. Let’s see some of them with examples.
We have inserted a time value! Really? No actually not. Please keep in mind, we start with (‘) this the value becomes text.
So, this is a text, not a time.
Let’s try using SECOND on this value.
See the result in the image below.
We have found the second. So, the SECOND function works on text!
Wait a minute, though this is a text, the value was a form of Excel valid time. That’s why the function derived the second.
Let’s imagine, you have a percentage value.
Do you think you can use SECOND on this?
Why thinking, just use it, see the outcome.
The SECOND function returns a number after using the percentage value. It converts the value into a valid time form and returns the second from it.
As it converts a value into a valid time and returns the second from it, but can we use a text string?
Use the function on this text Hello.
Oh! It returns an error. The SECOND function may convert a numeric value to valid time, but cannot convert text values to time format, hence cannot return a second from that.
You can insert the time value directly within the function.
Here we are inserting the time value. Note that you need to insert your time value within double quotes (
We have found the seconds from our given time.
You can use the NOW function as the argument of SECOND.
The NOW function returns the current date and time. When we use NOW inside SECOND, SECOND returns the current second from the time generated by NOW.
To visualize things better we are writing the NOW function to the adjacent column.
NOW returns the current time, and you can see the seconds are the same in both columns.
That’s all for today. From executing basic operations like extracting the second value to different nested operations can be performed by SECOND. We have tried showing you a couple of usages. Hope you will find this helpful.
Feel free to comment if anything seems difficult to understand. Let us know any of your SECOND function-related scenarios where you have stuck, we are ready to help.
- How to Use DAY Function in Excel (With 3 Examples)
- Use TODAY Function in Excel (6 Easy Examples)
- How to Use the Excel DAYS Function with a Practical Example
- Use DATEDIF Function in Excel (2 Examples)
- How to Use WEEKDAY Function in Excel ( With 8 Examples)
- Use NETWORKDAYS Function in Excel (3 Suitable Examples)