How to Use SECOND Function in Excel (3 Examples)

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).

overview - Excel SECOND Function

The SECOND function can be used for extracting seconds from different types of time input. Now without further ado, let’s begin.

Practice Workbook

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.

Function library

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.

function summary

Summary

Returns the second, a number from 0 to 59.

Syntax

SECOND(serial_number)

Arguments

serial_number: The time value to extract the second from. This should be a valid time that is recognized by Excel.

Time format

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. 

Version

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.

Extract second

We are going to fetch the second from here.

Our formula will be

=SECOND(time) 

formula - Excel SECOND Function

For simplicity, we have named time as our parameter.

Write the formula in Excel.

second extract - second Function

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!

Exercise SECOND

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.

Second difference Second Function

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.

Second difference result

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.

Exercise difference

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.

Nested formula Second Function

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.

MINUTE (serial_number)

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.

HOUR (serial_number)

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

=TIME(HOUR(time)+add_hour,MINUTE(time),SECOND(time))

Nested formula

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.

Nested formula result Second Function

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.

Exercise autofill

3. Quick Notes

The SECOND function can be used on different occasions. Let’s see some of them with examples.

Time in text

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.

SECOND on text time

See the result in the image below.

Result

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.

Percentage

Do you think you can use SECOND on this?

Why thinking, just use it, see the outcome.

SECOND on Percentage

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?

text string example

Use the function on this text Hello. 

SECOND on text

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.

time directly on SECOND

Here we are inserting the time value. Note that you need to insert your time value within double quotes (“”).

result for direct time input within SECOND

We have found the seconds from our given time.

You can use the NOW function as the argument of SECOND. 

NOW in 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. 

result for NOW in SECOND

To visualize things better we are writing the NOW function to the adjacent column.

NOW

NOW returns the current time, and you can see the seconds are the same in both columns.

Conclusion

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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo