Excel Convert Time to Seconds, Minutes, or Hours
To convert an Excel time value, multiply it by 86,400 for seconds, 1,440 for minutes, or 24 for hours. Excel stores times as fractions of a day, so these formulas work when the cell contains a real Excel time value—not text that merely looks like a time.
How Excel Stores Time
Excel represents a date and time as a serial number: the whole-number portion counts days, and the decimal portion represents the time within a day. For example, 6:00 AM is one-quarter of a day, or 0.25. One minute is 1/1,440 of a day; one second is 1/86,400.
This is why multiplying a time by the number of units in a day converts it to a total. A duration of 2 hours and 30 minutes is stored as 2.5/24 of a day. Multiplying by 24 returns 2.5 hours; multiplying by 1,440 returns 150 minutes; multiplying by 86,400 returns 9,000 seconds.
Excel may display the stored value as a clock reading, such as 2:30 AM, rather than as a decimal. The cell’s number format controls how a value appears; it does not change the underlying value.
Formulas for Seconds, Minutes, and Hours
If the Excel time is in A1, use one of these formulas:
- Total seconds:
=A1*86400 - Total minutes:
=A1*1440 - Total hours:
=A1*24
For example, if A1 contains 1:15:30, the results are 4,530 seconds, 75.5 minutes, or 1.258333… hours. The first two can be displayed as whole numbers if that precision suits your task; hours may need decimal places to retain useful detail.
To show a whole-number result, use =ROUND(A1*86400,0) for seconds or =ROUND(A1*1440,0) for minutes. Rounding changes the result, so keep the unrounded formula if later calculations need full precision.
Convert Elapsed Time Between Start and End
For a start time in A1 and an end time in B1, subtract the start from the end, then multiply by the conversion factor. For a duration that does not cross midnight, use =(B1-A1)*86400 for seconds, =(B1-A1)*1440 for minutes, or =(B1-A1)*24 for hours.
If a shift crosses midnight—for instance, it starts at 10:00 PM and ends at 6:00 AM—the straightforward subtraction is negative. If you know the end time is on the following day, use =MOD(B1-A1,1)*24 to return elapsed hours. Replace 24 with 1,440 or 86,400 for minutes or seconds. For records that can span a full day or more, enter full date-and-time values and subtract those instead; MOD alone wraps the result to less than one day.
When working with dates as well as times, subtract the complete date-time values. For example, =(B1-A1)*24 returns total elapsed hours, including any whole days between the timestamps.
Format the Result Correctly
A conversion formula can be correct while its result looks wrong because Excel applies a time format to the result cell. For example, a result of 4,530 may appear as a clock time or a date if the cell is formatted that way.
- Select the result cell or cells.
- Open the number-format options on the Home tab and choose General or Number.
- If you choose Number, set the decimal places you want to display.
For a duration displayed as hours, minutes, and seconds instead of a decimal, use a duration format such as [h]:mm:ss. The square brackets around h let the displayed hours exceed 24. A regular time format can wrap after a day, which hides the total duration. For decimal hours, use the multiplication formula and a Number format instead.
Text Values and Common Problems
Excel can calculate only if it recognizes the input as a number or time serial. A value imported from a text file may look like 00:12:30 but be stored as text. Multiplying that cell may produce an error or an unexpected result. Changing the cell format alone does not convert text into a numeric time.
To check, select the input and look at its alignment and the formula bar, or test it with =ISNUMBER(A1). A TRUE result indicates a numeric value. If the time is text in a consistent format, =TIMEVALUE(A1) can convert it; then multiply the result by the appropriate factor. If imported values include date information or inconsistent text, clean or parse the source data before calculating.
- Wrong output scale: Use 86,400 for seconds, 1,440 for minutes, and 24 for hours. These factors assume the input represents days.
- Negative elapsed time: Check whether the interval crosses midnight, or whether start and end cells have been reversed.
- Unexpected clock display: Change the output format to General or Number, or use a bracketed duration format where appropriate.
- Too many or too few decimals: Choose a suitable display precision, but distinguish formatting from rounding the underlying value.
Choosing the Right Conversion
Use total seconds for short intervals, event logs, or comparisons that need fine resolution. Total minutes are convenient for meeting lengths, task tracking, and many summary tables. Decimal hours suit timesheets and calculations where the next step is multiplying by an hourly quantity. Keep the unit consistent throughout a calculation: adding minutes directly to seconds, for example, produces a meaningless total unless you convert one first.
A converted value is a duration, not a time of day. “75 minutes” is an elapsed amount; “1:15” could mean a clock reading or a duration depending on the format and context. Label output columns with units such as Total seconds or Elapsed hours to avoid confusion. Also remember that a time-only entry does not record a date, time zone, or daylight-saving transition; use full timestamps when those details matter to the interval being measured.
Frequently Asked Questions
How do I convert hh:mm:ss to seconds in Excel?
For a numeric time value in A1, enter =A1*86400 and format the result as General or Number. For example, 00:12:30 becomes 750 seconds.
How do I convert Excel time to decimal hours?
Multiply the time value by 24: =A1*24. Format the result as Number to display decimal hours rather than a clock time.
Why does Excel show a date or time instead of a number?
The output cell likely has a date or time number format. Change it to General or Number; the formula may already be returning the correct numeric result.
How can I calculate minutes between two times?
For an interval that does not cross midnight, use =(B1-A1)*1440. If it crosses midnight and lasts less than 24 hours, use =MOD(B1-A1,1)*1440.
Why does multiplying my time give an error?
The input may be text rather than a numeric Excel time. Check with =ISNUMBER(A1); if it is text in a recognized time format, try converting it with TIMEVALUE before multiplying.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.