SECOND function

SECOND extracts the seconds component from a time value or timestamp, returning an integer from 0 to 59 for precise time-based analysis.

=SECOND(serial_number)

Generate a SECOND formula

Describe what you need. The generator will reach for SECOND where SECOND is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SECOND reads its arguments
serial_numberrequiredSECOND →
ArgumentRequiredDescription
serial_numberRequiredA time value, timestamp, or time text string such as '14:30:45'. Can be a cell reference, direct input, or result of TIME() or NOW(). Returns #NUM! if negative or exceeding 1 (times must fall between 0 = midnight and 1 = 23:59:59). Returns #VALUE! if the input is text that cannot be parsed as time.

Returns

Returns an integer from 0 to 59 representing the seconds within the minute.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Audit the exact second a vehicle service was logged

VehicleOdometerService DateCostGarage
Van A450002025-03-15 08:30:45250Quick Fit
Bus B1200002025-03-16 14:22:15450Premier Auto
Truck C850002025-03-17 09:15:30375Quick Fit
Van A465002025-03-20 16:45:00200Premier Auto
Bus B1215002025-03-21 10:05:22520Quick Fit
=SECOND(C2)

Result: 45

Applied to C2 containing '2025-03-15 08:30:45', SECOND returns 45, isolating just the seconds portion. This precision is essential when auditing maintenance logs to verify the exact moment within a minute when each service was recorded. Van A's service was performed at 8:30 and 45 seconds.

2. Identify maintenance recorded at exact minute boundaries

VehicleOdometerService DateCostGarage
Van A450002025-03-15 08:30:45250Quick Fit
Bus B1200002025-03-16 14:22:15450Premier Auto
Truck C850002025-03-17 09:15:30375Quick Fit
Van A465002025-03-20 16:45:00200Premier Auto
Bus B1215002025-03-21 10:05:22520Quick Fit
=IF(SECOND(C4)=0,"Exact boundary","Mid-minute")

Result: Exact boundary

Cell C4 contains '2025-03-20 16:45:00'—recorded at exactly 45 minutes and zero seconds. The IF statement checks if SECOND equals 0, which catches this perfect boundary. This distinguishes automatically timestamped entries (likely to land on 0 seconds) from manually entered times (scattered throughout the minute), helping validate data quality and logging mechanisms.

3. Analyze the average second offset across a week of services

VehicleOdometerService DateCostGarage
Van A450002025-03-15 08:30:45250Quick Fit
Bus B1200002025-03-16 14:22:15450Premier Auto
Truck C850002025-03-17 09:15:30375Quick Fit
Van A465002025-03-20 16:45:00200Premier Auto
Bus B1215002025-03-21 10:05:22520Quick Fit
=AVERAGE(SECOND(C2:C6))

Result: 24.4

Extracting seconds from all five timestamps yields 45, 15, 30, 0, and 22 seconds. Averaging these gives 24.4 seconds, revealing that maintenance was logged fairly randomly throughout each minute—neither systematically early (clustered near 0) nor late (near 59). This pattern analysis helps identify logging anomalies and understand whether timestamps are automatic or manual.

Common errors

Which SECOND error are you seeing?
SECOND returned an error#VALUE!
Ensure the argument is a proper time value like TIME(14,30,45), time text in HH:MM:SS format ('14:30:45'), or a cell containing a full timestamp with both date and time. Use TIMEVALUE() to convert text to time first if needed.
#NUM!
Verify the input represents a time between 0 (midnight) and 1 (23:59:59 the same day). If using TIME() or NOW(), those automatically return values within this range. If using decimal time, calculate the decimal as hours/24, ensuring the result stays between 0 and 1.
#REF!
Update the formula to reference an existing cell containing a valid time value. Verify the row number exists in your current table, and recalculate any dynamic ranges if rows were inserted, deleted, or moved.
ErrorWhy it happensHow to fix it
#VALUE!You pass text that isn't recognized as a valid time format. For example, =SECOND('2025-03-15') with only a date and no time, or =SECOND('Van A') with non-time text.Ensure the argument is a proper time value like TIME(14,30,45), time text in HH:MM:SS format ('14:30:45'), or a cell containing a full timestamp with both date and time. Use TIMEVALUE() to convert text to time first if needed.
#NUM!The serial_number falls outside the valid range for times. For example, =SECOND(-0.25) with a negative number, or =SECOND(1.5) where the value exceeds 1 (the limit for a 24-hour period).Verify the input represents a time between 0 (midnight) and 1 (23:59:59 the same day). If using TIME() or NOW(), those automatically return values within this range. If using decimal time, calculate the decimal as hours/24, ensuring the result stays between 0 and 1.
#REF!You reference a cell that no longer exists, has been deleted, or moved. For example, =SECOND(C99) when the source table only has 6 rows, or referencing a cell in a deleted worksheet.Update the formula to reference an existing cell containing a valid time value. Verify the row number exists in your current table, and recalculate any dynamic ranges if rows were inserted, deleted, or moved.

Tips and when to use something else

  • Use HOUR and MINUTE for larger time components; SECOND returns only 0–59, making it ideal when you need seconds-level precision for auditing event timestamps.
  • Combine SECOND with NOW() to extract the current second from your system clock: =SECOND(NOW()) updates live, useful for real-time logging and performance monitoring.
  • If you only need the minutes portion, use MINUTE() instead—it's clearer and more direct than extracting and converting SECOND results, reducing complexity.
  • Remember times are stored as decimals (0.5 = noon, 0.25 = 6 AM). For text times, wrap with TIMEVALUE() first: =SECOND(TIMEVALUE('14:30:45')) ensures reliable results.

Related functions

MINUTEMINUTE extracts the minute component (0-59) from a time value, returning an integer representing the minutes elapsed in the current hour.HOURHOUR extracts the hour component from a time value, returning a number between 0 and 23 representing the hour of the day.TIMETIME converts hours, minutes, and seconds into a decimal time value between 0 and 1, representing a time on a 24-hour clock.NOWReturns the current date and time as a timestamp value that updates automatically each time the spreadsheet recalculates.TIMEVALUEConverts time text into a decimal number enabling calculations with warehouse receipt times, shipping schedules, and processing timestamps.

Frequently asked questions

Why does SECOND return 0 when my timestamp shows ':00' seconds?
Because that's the actual value—a timestamp ending in ':00' genuinely has 0 seconds. If you expected something different, verify the source timestamp includes seconds. When seconds aren't displayed or entered, they default to 0 (midnight position in the minute).
Can I use SECOND on a date without a time component?
Yes, but it always returns 0. A date alone (e.g., '2025-03-15') is treated as '2025-03-15 00:00:00' at midnight. To extract non-zero seconds, include the time in your input: =SECOND('2025-03-15 14:30:45') or reference a cell with a full timestamp.
How is SECOND different from MINUTE?
SECOND returns the seconds component (0–59), while MINUTE returns the minutes component (0–59). For the time '14:30:45', SECOND returns 45 and MINUTE returns 30. Choose whichever time component your analysis requires.
How can I use SECOND to round times to the nearest minute?
Use =IF(SECOND(time)>=30, time + TIME(0,1,-SECOND(time)), time - TIME(0,0,SECOND(time))). This adds a minute and subtracts seconds if seconds ≥ 30, or subtracts seconds if < 30, rounding the time to the nearest minute boundary automatically.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17