Somewhere to document technical bits and pieces that I think might be useful.

Menu

Convert seconds to HH:MM:SS format in Excel

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: