1

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

Riiz87
  • 21
  • 2

2 Answers2

0

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.

0x5C91
  • 3,360
  • 3
  • 31
  • 46
  • so, to summarize, I can not get a direct formatting on the returned result. rather I need to add an extra column to get the desired formatting, right? – Riiz87 Dec 04 '21 at 06:47
-1

try:

=INDEX(TEXT(A2:A5/86400, "[h]:mm:ss"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124