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
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.
See also How to format a duration as HH:mm in the new Google sheets for custom formatting of the duration.
