Convert Seconds to Time (hh:mm:ss) in Excel

To convert seconds to time in Excel, divide the number of seconds by 86400 and format the result as [h]:mm:ss. So 3,725 seconds becomes 1:02:05, and 95,000 seconds becomes 26:23:20. This guide is for anyone who gets durations as raw seconds, from call logs, video lengths, app exports or race results, and needs to read them as hours, minutes and seconds.

Why you divide seconds by 86400#

Excel stores dates and times as numbers, where 1 means one whole day. Noon is 0.5, because it is half a day. A time is just a fraction of 1.

A day has 24 × 60 × 60 = 86,400 seconds. So to turn seconds into Excel's time scale, divide by 86400:

=A2/86400

If A2 holds 3725, the result is about 0.0431, which is 0.0431 of a day. It looks like a meaningless decimal until you give it a time format. The guide to how Excel stores times explains the same idea for subtracting two times.

How to convert seconds to hh:mm:ss with a format#

A number format changes how a value looks without changing the value. Here are the steps:

1. In B2, type =A2/86400 and fill it down (Windows: Ctrl+D; Mac: ⌘D).
2. Select the results and open Format Cells (Windows: Ctrl+1; Mac: ⌘1).
3. Choose Custom and type [h]:mm:ss in the Type box.
4. Click OK.

The value in B2 is still a number, so you can add, average or chart it. That is the main reason to prefer a format over text. For example, =SUM(B2:B4) with the same [h]:mm:ss format shows the total time of all three rows, 27:26:10, and =AVERAGE(B2:B4) shows the average duration. An average of 9:08:43 is easier to read than an average of 32,923.33 seconds, and it is the same value. For more format codes, see custom number formats in Excel.

Why you need [h] for more than 24 hours#

Square brackets around the hours code show the total hours instead of the hours of the day. Without them, Excel treats each full 24 hours as a day and hides it.

95,000 seconds is 26 hours, 23 minutes and 20 seconds. The plain h format drops one full day and shows 2:23:20, which is wrong for a duration. Use [h] for any total that might pass a day. The same rule applies when you add hours to a time and the total runs past midnight.

Brackets work on minutes and seconds too. [m]:ss shows total minutes: 3,725 seconds becomes 62:05. [s] shows total seconds.

How to convert seconds to time as text with TEXT#

The TEXT function applies a number format inside a formula and returns text. Use it when you want to join the duration with words or export it as it looks:

=TEXT(A3/86400,"[h]:mm:ss")

This returns 26:23:20 as text. You can then build a sentence:

="Call length: "&TEXT(A2/86400,"[h]:mm:ss")

That gives "Call length: 1:02:05". The catch is that text can't be added or averaged, so keep a numeric version for calculations. The TEXT function guide and the article on TEXT function format codes list the codes you can use.

Show days, hours, minutes and seconds#

For long durations, you may want days shown on their own. Split the seconds with INT and MOD:

=INT(A3/86400)&"d "&TEXT(MOD(A3,86400)/86400,"hh:mm:ss")

For 95,000 seconds this returns 1d 02:23:20.

• INT(A3/86400) counts the full days: 1.
• MOD(A3,86400) is what is left after removing full days: 8,600 seconds.
• TEXT formats those 8,600 seconds as 02:23:20.

You can also build hours, minutes and seconds by hand, which works in any tool that lacks time formats:

=INT(A3/3600)&":"&TEXT(INT(MOD(A3,3600)/60),"00")&":"&TEXT(MOD(A3,60),"00")

This also returns 26:23:20. The guide to MOD and INT explains how these two split a number into whole parts and remainders.

Seconds stored as text, decimals and milliseconds#

Imported data often arrives in awkward shapes. Here is how to handle the common ones:

• Seconds stored as text. A value like '4500 sits on the left of the cell. Arithmetic usually converts it, so =A5/86400 still works, but you can make it explicit with =VALUE(A5)/86400. Either way, 4,500 seconds is 1:15:00.
• Decimal seconds. For 3725.6 seconds, use the format [h]:mm:ss.0 to show tenths of a second.
• Milliseconds. Divide by 86,400,000 instead: =A2/86400000, then format as [h]:mm:ss.000.
• Minutes, not seconds. Divide by 1440, the number of minutes in a day.

Common mistakes when converting seconds to time#

Most wrong results come from the format, not the formula. Check these:

1. A long decimal shows instead of a time. The cell still has the General format. Apply [h]:mm:ss.
2. Hours reset after 24. You used h instead of [h].
3. You divided by 60 or 3600. That gives minutes or hours as a plain number, not a time value. Formatting it as time then shows nonsense.
4. TIME(0,0,A2) fails. TIME's seconds argument can't go above 32,767, so larger values return #NUM!, and its result wraps back to 0 after 24 hours. Dividing by 86400 has neither problem.
5. A SUM of text durations returns 0. Durations made with TEXT are text. Add the numeric versions instead, then format the total: =TEXT(SUM(A2:A4)/86400,"[h]:mm:ss") gives 27:26:10 for the three rows above.

To go the other way, from a time back to seconds, multiply by 86400. A cell holding 1:02:05 times 86400 gives 3725.

Practise it#

Converting seconds is really two skills: knowing that Excel stores time as part of a day, and controlling how a number looks. Practise how Excel stores dates as numbers in a live spreadsheet, then practise TEXT and VALUE to switch between numbers and text. ExcelReady checks each formula as you type. Start with the 95,000-second exercise on this page.