Cellwise Start free

Excel guide · 4 min read

Convert seconds to hh:mm:ss in Excel

Quick answerDivide the seconds by 86400 (the seconds in a day) and format the cell as [h]:mm:ss: =C2/86400 turns 21,645 seconds into 6:00:45. To go back, multiply a time by 86400. For a text label in one step, use =TEXT(C2/86400,"[h]:mm:ss").

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEF
1AgentCallsTalk (sec)Hold (sec)AHT exportLongest call
2Maya5821645173006:130:41:07
3Jonas6119870221505:261:12:30
4Priti472241098007:570:38:52
5Owen6618905312004:460:29:15
6Leah5220330140506:310:55:40
7Sami3923760265010:091:03:18

Download this sheet as an Excel practice file (.xlsx)

Divide by 86400 and format the cell

Phone systems, video tools and fitness trackers usually export durations as plain seconds. Excel measures time in days, so one second is 1/86400 of a day (24 x 60 x 60). Dividing by 86400 turns seconds into a real time value that Excel can format, add and average.

The result looks like 0.25 until you format it. Select the cells, open Home > Number Format > More Number Formats > Custom and type [h]:mm:ss. The value stays a number, so it still works in SUM and AVERAGE.

=C2/86400

Result: 0.25052083333 shows as 6:00:45 with the custom [h]:mm:ss format

Maya's 21,645 seconds of talk time is 6 hours, 0 minutes and 45 seconds.

Totals over 24 hours: [h]:mm:ss

The six agents together talked for 126,920 seconds. That is more than a day, and this is where the square brackets matter. With h:mm:ss, Excel shows only the part after the whole days, so a team total looks like a normal shift.

Sum the seconds first, then divide once. Dividing each row and summing gives the same answer, but one division is easier to read and audit.

=TEXT(SUM(C2:C7)/86400,"[h]:mm:ss")

Result: 35:15:20

35:15:20. With "h:mm:ss" the same total shows 11:15:20, silently dropping a whole day.

Average handle time as m:ss

Call centres report average handle time (AHT) in minutes and seconds. Divide talk seconds by calls to get seconds per call, then by 86400. The m:ss format suits values under an hour.

For the team figure, divide total seconds by total calls. Averaging the six agent AHTs instead gives each agent equal weight, even though Owen took 66 calls and Sami 39. The weighted figure is the one that matches the phone system.

=TEXT(SUM(C2:C7)/SUM(B2:B7)/86400,"m:ss")

Result: 6:33

6:33 per call across the team. If a value can pass 60 minutes, use [m]:ss so the minutes keep counting: total hold time shows as 201:40.

Back to seconds: multiply by 86400

To turn a real time value back into seconds, multiply by 86400 and round to whole seconds, because time values carry floating-point tails. The same works for any cell that holds a true time, whether you typed 1:12:30 or it came from a formula.

Use seconds (or decimal minutes) when you need to compare, rank or chart durations, or feed them into a rate such as cost per minute. Use the [h]:mm:ss display when people need to read them.

=ROUND(TIMEVALUE(F3)*86400,0)

Result: 4350

4,350 seconds for Jonas's longest call of 1:12:30. F3 is text, so TIMEVALUE converts it first; on a real time you would use =ROUND(F3*86400,0).

Text durations from exports, and the mm:ss trap

Columns E and F came out of a CSV as text. You can tell because they sit on the left of the cell and =SUM(E2:E7) returns 0. TIMEVALUE turns text like "1:12:30" into a time. SUMPRODUCT(TIMEVALUE(F2:F7)) adds the whole column without a helper.

The trap: when a duration has only two parts, Excel reads it as hours and minutes. "06:13" meant 6 minutes 13 seconds, but TIMEVALUE("06:13") is 6:13 AM, and times 86400 gives 22,380 seconds instead of 373. Put "0:" in front so Excel sees zero hours. If a column mixes "06:13" and "1:02:03", add the prefix only to the short ones: =ROUND(TIMEVALUE(IF(LEN(E2)<=5,"0:"&E2,E2))*86400,0).

=ROUND(TIMEVALUE("0:"&E2)*86400,0)

Result: 373

373 seconds, which matches Maya's 21,645 talk seconds divided by 58 calls.

Common mistakes

Formatting with h:mm:ss for totals

Anything over 24 hours wraps around. Use [h]:mm:ss for totals, and [m]:ss when you want total minutes.

Reading mm:ss text as hh:mm

TIMEVALUE("06:13") is 6 hours 13 minutes. Prefix "0:" so two-part durations are read as minutes and seconds.

Summing the TEXT results

TEXT returns text, and SUM skips text, so the total is 0. Keep the numeric value (seconds/86400) in the cell and format it, or sum the seconds before you call TEXT.

Dividing by 3600 and formatting as time

Seconds/3600 gives decimal hours (6.01), which is a number, not a time. Divide by 86400 if you want a time format, by 3600 if you want decimal hours.

FAQ

How do I convert seconds to hh:mm:ss in Excel?

Use =A2/86400 and format the cell with the custom format [h]:mm:ss. 3,723 seconds then shows as 1:02:03.

Why 86400?

Excel counts time in days, and a day has 24 x 60 x 60 = 86,400 seconds. Dividing by 86400 turns seconds into the fraction of a day that Excel can format as a time.

How do I convert hh:mm:ss back to seconds?

Multiply by 86400 and round: =ROUND(A2*86400,0). If the duration is text, convert it first with =ROUND(TIMEVALUE(A2)*86400,0).

How do I show seconds as minutes and seconds only?

Use the m:ss format for values under an hour, or [m]:ss to keep counting minutes past 60. =TEXT(12100/86400,"[m]:ss") shows 201:40.

How do I convert seconds to decimal hours?

Divide by 3600: =ROUND(C2/3600,2) gives 6.01 hours for 21,645 seconds. Use this when you multiply by an hourly rate.

Functions used

Related guides

Updated