How to format a number as HH:MM in google sheets-CodePudding

I have Data set in google sheets that shows time spent log for a user in SECONDS. For example, Name (text) | Time Spent (number) user A | 86400

How can format the time spent field as 24:00:00

Followed so many format related articles but all I am getting is 2073600:00:00 or similar weird numbers

CodePudding user response：

You need to first divide by 86400, then set Format > Number > Duration. This is because the duration is evaluated in days (a value of 1 is formatted as 24 hours, 0 minutes, 0 seconds).

Example: if cell A1 contains the value of "3600" (seconds), you can display it as a duration in A2 by setting the value of A2 to "=A1/86400", then changing the format of A2 to Duration. A2 will display a duration of 1 hour in the default format.