How to use the SECOND function
What is the SECOND function?
The SECOND function returns an integer representing the second based on an Excel time value. The returning number ranges from 0 to 59.
What is an integer?
An integer is a whole number that can be positive, negative, or zero, but not a fraction or decimal. Excel can't calculate the second based on a negative Excel time value.
What is an Excel time value?
Excel time is actually a decimal number ranging between 0 and 1 in Excel and then formatted as time.
For example, 12:00 PM is represented as 0.5 because it is half of a day, you can verify this by typing 12:00 PM in a cell and then change the cell formatting to general. This will show the value as Excel interprets it.
How does Excel recognize time values?
Excel recognizes certain text strings like "6:45 PM" as valid time values. A recognized time value is right aligned in the cell just like a regular number, shown in the image below in cell B2.
A time number that is not recognized is left aligned which is demonstrated in cell B4 in the image above. This visual feedback lets you easily spot values that need closer inspection.
What is a second in an Excel time value?
There are 60 minutes in one hour and 24 hours in one day. 60 * 24 = 1440 minutes in one day. There are 60 seconds in one minute, an entire day contains 60 * 1440 = 86,400 seconds.
1/86400 or approx. 0.0000115740740740741 represents one second in Excel time value. Excel uses a number 0 <= x <=Â 1 in decimal form to represent time in an Excel worksheet. 0 is zero minutes and 1 is 86400 seconds (24 hours).
Why is 1 equal to 86,400 seconds?
This has to do how Excel handles dates. Each date is represented by an integer and one day is equal to 1 in Excel. There are 24 hours, or 1440 minutes, or 86,400 seconds in one day.
Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.
For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.
This allows dates to easily be formatted to display in many date/time formats like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.
You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.
SECOND function Syntax
SECOND(serial_number)
SECOND function Arguments
serial_number | Required. An Excel time value that you want to extract the second from. |
SECOND function Example
Formula in cell C3:
Comments
Excel time (serial_number)Â is actually a number ranging between 0 and 1 in Excel and then formatted as time.
Example, 12:00 PM is represented as 0.5 because it is half of a day, you can verify this by typing 12:00 PM in a cell and then change the cell formatting to general.
This will show the value as Excel interprets it.
'SECOND' function examples
The following article has a formula that contains the SECOND function.
Table of Contents How to AVERAGE time How to enter an array formula Explaining formula How to AVERAGE time hh […]
Functions in 'Date and Time' category
The SECOND function function is one of 22 functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form