CODEWITHSUNDEEP
google-sheets
Mathias
Mathias

Reputation: 4569

Google sheets duration to seconds

I have a cell formatted as a duration (6:49:00)
I want to convert that to an Integer of total seconds
This formula gives me the right number of seconds =C3*60
409:00:00 <-- But I want this as just 409

Upvotes: 43

Views: 62009

Answers (4)

Dave
Dave

Reputation: 21

Old thread but maybe helps someone:

  1. Multiply any duration (HH:MM:SS) by 86400 (number of seconds in a day)

  2. Format as a number: Format > Number > Number

    =C3*86400

Upvotes: 2

user3717023
user3717023

Reputation:

To convert duration to an integer expressing the number of seconds, use a formula such as 

=value(A1*24*3600)

Time values are recorded so that 1 is one day. Multiplying by 24 (hours/day) and 3600 (seconds/hour) converts that to seconds. Then value makes it a number rather than duration.

Old answer, about formatting only.

You don't need any formulas to format duration as the number of seconds.

  1. Go to Format > Number > More formats > More data and time formats
  2. Delete the pre-filled format fields and add "Elapsed seconds" from the dropdown menu.

Upvotes: 65

Clemens Schwaighofer
Clemens Schwaighofer

Reputation: 131

=HOUR(A1) will NOT work if your hours in the duration is > 24 of course. So the last example is not correct.

What will work is the following.

Given: A duration in hours and minutes. eg 225:04 or 9:20 or 62:35 Format must be set as this (Elapsed hours:minutes)

=INDEX(SPLIT(A1, ":"), 0, 1)*60 + INDEX(SPLIT(A1, ":"), 0, 2)

Upvotes: 7

Bohdan Dmytriv
Bohdan Dmytriv

Reputation: 408

I have found this solution:

let the cell A1 filled with duration like 1:22:33, than formula

=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)

will do the trick.

For example, 1:01:01 -> 3661

Upvotes: 23

Related Questions