I'm calculating some sums of gain over time - therefore I want to divide X by time required to generate X.

image description

The problem here is, that the SECOND function in the =(SUM(B4:B18)/SECOND(SUM(A4:A18)))*60 formula returns second portion (mod 60), not second sum (with hours*3600 and minutes*60).

So how can I convert duration field into integer representing number of seconds for that duration?


If your 0:01:00 is a date/time index (numeric rather than a string) the underlying number is an integer for days and a decimal for time. as there are 24*60*60 seconds in a day the number of seconds represented by 0:01:00 is effectively the number of seconds between midnight and one minute past midnight, which for A1 can be calculated as:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.