Purpose
Return value
Syntax
=SECOND(serial_number)
- serial_number - A valid time in a format Excel recognizes.
Using the SECOND function
The SECOND function extracts the second component from a time as a number between 0-59. For example, given a time of “12:15:01”, SECOND will return 1. The SECOND function takes just one argument , serial_number , which must be a valid Excel date , a valid Excel time , or a text value Excel can interpret as a time (e.g. “7:45:30 PM”).
The SECOND function does not convert time into seconds but rather extracts the seconds component from time. For example, given a time duration of 10 minutes (600 seconds) the SECONDS function will return 0 (zero), since seconds are zero in the time 10:00. This means the SECOND function will “reset” to 0 every 60 seconds (like a clock). To convert a time value into decimal seconds, see this example . To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function .
Examples
When given the time “10:45:17 AM”, the SECOND function will return 17:
=SECOND("10:45:17 AM") // returns 17
With the time “2:19:36” in cell A1, SECOND will return 36:
=SECOND(A1) // returns 36
The formula below demonstrates how the TIME function can be used to create the time 9:30:45 in Excel:
=TIME(9,30,45)
If we wrap the SECONDS function around the TIME function, we get 45, as expected:
=SECOND(TIME(9,30,45)) // returns 45
Note: Excel stores dates as serial numbers and times as decimal numbers . For example, the time 12:00 PM is equal to 0.5 (one half-day), and the date Jan 1, 2000 12:00 PM is equal to the serial number 32526.5 in Excel.
Fractional seconds
One notable limitation of the SECOND function is that it rounds fractional seconds to the nearest second. You can see this behavior in the worksheet below. The SECOND function is configured to extract seconds from the times in column C, which are recorded in hundredths of a second. During this operation, the fractional part of the number is lost when it is rounded:
To extract fractional seconds while maintaining precision, you can use a formula like this:
=MOD(C5*1440,1)*60
For a detailed explanation of this formula, see Time in hundredths of a second .
Purpose
Return value
Syntax
=TIME(hour,minute,second)
- hour - Number of hours.
- minute - Number of minutes.
- second - Number of seconds.
Using the TIME function
The TIME function creates a valid Excel time using the given values for hour , minute , and second . Like all Excel time, the result is a number that represents a fractional day . The TIME function will only return time values up to one full day, between 0 (zero) to 0.99999999, or 0:00:00 to 23:59:59. To see results formatted as time, apply a time-based number format .
The main benefit of the TIME function is the convenience of entering a time with separate values for hours, minutes, and seconds. It is analogous to the DATE function , which creates dates with separate values for year, month, and day.
Basic example
=TIME(3,0,0) // 3 hours
=TIME(0,3,0) // 3 minutes
=TIME(0,0,3) // 3 seconds
=TIME(8,30,0) // 8.5 hours
Time formatting
The TIME function returns a valid time as a number, but the way Excel displays the result depends on number formatting . Excel can display that result as time with AM/PM suffix, a 24-hour time like 17:30:00, or as a regular number. You can see how these formats compare in the screen below, where the number formatting applied to columns F, G, and H is as follows:
h:mm AM/PM
h:mm:ss
0.0
Number formatting affects the display of time only, not the underlying value .
Larger units
The TIME function can interpret units in larger increments. For example, both of the formulas below return 2 hours:
=TIME(0,120,0) // 2 hours
=TIME(0,0,7200) // 2 hours
However, TIME will not handle numeric inputs larger than 32,767. For example, even though there are 86,400 seconds in a day, the following formula (which represents 12 hours) will fail with a #NUM! error:
=TIME(0,0,43200) // returns #NUM!
Time over 24 hours
It’s important to understand that TIME only returns results up to 24 hours . When the total time exceeds 24 hours, the TIME function will “reset” to zero.
=TIME(12,0,0) // returns 0.5 (12 hours)
=TIME(18,0,0) // returns 0.75 (18 hours)
=TIME(24,0,0) // returns 0 (0 hours)
=TIME(36,0,0) // returns 0.5 (12 hours)
=TIME(48,0,0) // returns 0 (0 hours)
Notice TIME returns 0 for 24 hours and 48 hours above. In this way, the behavior of the TIME function is similar to a 24-hour clock that resets at midnight. To create time durations greater than 24 hours, you can use a manual formula to convert hours , minutes , and seconds to Excel time like this:
=(hours/24)+(minutes/1440)+(seconds/86400)
The result from the formula above is the same as with the TIME function for time durations up to 24 hours. For time durations over 24 hours, this formula will continue to work correctly while the TIME function will reset to zero at each 24-hour interval. For example, for a time duration of 72 hours, TIME returns zero:
=TIME(72,0,0) // returns 0
However, the alternate formula above will correctly return 3 days:
=(hours/24)+(minutes/1440)+(seconds/86400)
=(72/24)+(0/1440)+(0/86400)
=(3)+(0)+(0)
=3
When formatted with the custom number format [h]:mm:ss. Excel will display 72:00:00.
This formula is also useful for creating time that includes fractional seconds .
Negative inputs
Although the TIME function will return an error if the final result is negative, you can supply units as negative numbers so long as the result is positive. For example:
=TIME(12,-3,0) // returns 09:00:00
=TIME(24,-1,0) // returns 23:59:00
=TIME(3,-120,0) // returns 01:00:00
TIME function limitations
There are two limitations to the TIME that you should be aware of. First, TIME will only accept whole numbers for hours, minutes, and seconds. If you supply decimal values, the decimal portion of the number will be discarded. For example, both formulas below return 12 hours even though the second formula provides 12.5 for hours:
=TIME(12,0,0) returns 12 hours
=TIME(12.5,0,0) returns 12 hours
Second, TIME only supports positive time. If you provide inputs that would create a negative time, TIME will return a #NUM! error:
=TIME(-6,0,0) // returns #NUM!
Notes
- When the total time reaches 24 hours, the TIME function will “reset” to zero.
- The largest number that TIME will allow for hour, minute, or second is 32,767. Larger values will return a #NUM! error.
- If hours, minutes, or seconds are provided as a negative number, TIME will return a #NUM! error.
