I need to know how to do this today but couldn’t find an easy copy and paste answer, so I had to make one myself.

Other solutions I have seen use the TIME function will work so long as the number of hours is less than 24, once they are higher then it will fail as it’s actually using a date format and just showing the time portion of it (and date formats cannot have more than 23 hours in a day). E.g. 86401 seconds (a day plus one second) will show “00:00:01″.

The way around it is to use a bit of maths (floor and mod) and build the hours, minutes and seconds up with concatenation. For example, in a new Excel document set the following cells:

A1=86401

A2=CONCATENATE(TEXT(FLOOR.MATH(A1/60/60),”00″),”:”, TEXT(FLOOR.MATH(MOD(A1,60*60)/60),”00″),”:”, TEXT(FLOOR.MATH(MOD(A1,60)),”00″))

(Note, the formula for A2 above should all be on one line.)

This will give a value of “24:00:01” which is what I needed.

Note, I’m purposely formatting the H, M and S values with two digits, but if the number of hours is larger than 99 then it will expand to three or more figures.

Hope someone finds this useful!