Convert Excel Time to Decimal Seconds
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This page explains how to convert a time value stored in Excel (like 2:30:15) into decimal seconds (like 9035 seconds). You enter the time in Excel’s time format or as text, and the calculator returns the total number of seconds as a plain number, which is useful for payroll, data analysis, or scientific calculations.
Formula
Excel stores times as fractions of a 24-hour day. One day equals 1, so one hour equals 1/24, one minute equals 1/(24*60), and one second equals 1/(24*60*60). To convert a time value to decimal seconds, you multiply the time by 86,400 (the number of seconds in a day):
Total seconds = Time × 86400
Where Time is the Excel time value (a number between 0 and 1 for times within a day). For example, 12:00:00 (noon) is stored as 0.5, so 0.5 × 86400 = 43,200 seconds.
Worked Example
Using the default value of 2:30:15 from the calculator:
- Break the time into hours, minutes, and seconds: 2 hours, 30 minutes, 15 seconds.
- Convert each part to seconds: 2 × 3600 = 7200, 30 × 60 = 1800, 15 × 1 = 15.
- Add them: 7200 + 1800 + 15 = 9015 seconds.
So 2:30:15 equals 9,015 seconds. The calculator gives the same result using the formula Time × 86400.
Why Convert Time to Decimal Seconds?
Excel’s time format is great for display but awkward for arithmetic. If you need to calculate average speed (distance divided by time), total pay (hours × rate), or combine durations from different sources, decimal seconds make the math straightforward. For instance, if you have a list of lap times in mm:ss and want the total, converting each to seconds first avoids errors from Excel’s base-60 system.
How to Do It in Excel (Step by Step)
If you prefer to do the conversion directly in a worksheet, here are three reliable methods:
- Multiply by 86400: If cell A1 contains a time value (e.g., 2:30:15), enter
=A1*86400in another cell and format the result as a number. This works because Excel treats times as day fractions. - Use HOUR, MINUTE, SECOND functions: Enter
=HOUR(A1)*3600 + MINUTE(A1)*60 + SECOND(A1). This method is more explicit and also works if the time is stored as text (as long as the text is recognized as a time). - Convert text times: If your time is text like “2:30:15”, use
=VALUE(A1)*86400after ensuring the text is in a time format Excel understands. If not, use=TIMEVALUE(A1)*86400.
Common Mistakes and How to Avoid Them
The most frequent error is forgetting that Excel times are fractions of a day. If you simply multiply 2:30:15 by 60, you get 150.25, which is meaningless. Another pitfall is formatting the result as a time instead of a number—you’ll see something like 2:30:15 again, not 9015. Always set the cell format to General or Number before applying the formula.
Also note that times exceeding 24 hours are stored as values greater than 1. For example, 26:00:00 is stored as 1.08333. The multiplication method still works, but the HOUR function will return the remainder (2) rather than the total hours (26). For durations over 24 hours, use the multiplication method or the DAY function to extract whole days.
Limitations of the Formula
The basic formula assumes your time is a valid Excel time value. If you have times that include milliseconds or are in a non-standard format, you’ll need to adjust. Also, Excel’s precision is about 15 significant digits, so for very large times (like centuries) you may see rounding errors. For most practical purposes—work shifts, race times, video lengths—the formula is exact to the second.
Frequently Asked Questions
How do I convert a time like 1:30 PM to seconds?
First, convert the time to a 24-hour format (13:30:00). Then multiply by 86400: 13.5 hours × 3600 = 48,600 seconds. In Excel, if the cell contains 1:30 PM, the formula =A1*86400 will return 48,600 because Excel internally stores it as 0.5625.
What if my time is in mm:ss and has no hours?
If you enter 5:30, Excel may interpret it as 5 hours and 30 minutes, not 5 minutes and 30 seconds. To avoid this, enter it as 0:05:30 or use a custom format. Alternatively, multiply the minutes by 60 and add seconds manually.
Can I convert a time to seconds without a formula?
Yes, you can use Excel’s built-in functions in the Formula tab, but there’s no one-click button. You can also use the TEXT function to extract components, but that’s more complex. The simplest approach is a formula.
Why does my result show as a date or time?
That happens when the cell is formatted as a date or time. Right-click the cell, choose Format Cells, select Number, and set decimal places to 0. The underlying value is correct; only the display is wrong.
How do I convert a duration that exceeds 24 hours?
Use the multiplication method (=A1*86400). For example, 26:00:00 stored as 1.08333 × 86400 = 93,600 seconds. Avoid using HOUR, MINUTE, SECOND functions for such durations because they only return the remainder within a day.