youtube-dl output time duration addition

English
, ,
#1

Hi. I apologize if this question is a bit basic. I want to perform a simple calculation in LibreOffice Calc. I used youtube-dl to create a text document of the duration of each video in a given playlist. I want to use LibreOffice Calc to add the resulting time duration data. Each column has a video time presented in this format:

1:07:25
53:59
36

It follows in the order of hours, then minutes, then seconds. If there is no hour, then simply minutes and seconds. And occasionally just seconds if it’s a short video.

I’m wondering if it’s possible to set a formula to import a plaintext document and calculate the addition of these time durations in this format?

#2

Assuming that your 1:07:25 53:59 36 is not one cell content but three examples that can occur as content and each row has only one time …

Make sure that when importing the file (best name the file name extension .csv so the proper filter is used when opening the file) the column with times is imported as Text type, in the import dialog click the data column’s header and explicitly set type to Text. Then, assuming your times are in column A, use this formula for example in B1

=IF(LEN(A1)>=7;TIMEVALUE(A1);IF(LEN(A1)>=4;TIMEVALUE("0:"&A1);TIMEVALUE("0:0:"&A1)))

and format the result as duration [HH]:MM:SS then copy&paste this cell down to end of data. To sum all durations simply do =SUM(B1:B99) (or wherever your data ends instead of row 99) and again format the result as duration [HH]:MM:SS

#3

You are correct in assuming the three time examples were intended to be posted below one another.

Thank you for the well-written help, but I am still confused. Sorry I have not used LibreOffice Calc before so it is intimidating for me to use. I have imported the .csv file as Text type. But I am unsure as to what the next step is. From what I understand, I copy&paste the formula into the B1 cell?

Here is a screenshot to give you a view of what my document looks like: https://files.catbox.moe/8qschr.png

#4

Copy&paste the formula to B1. Then copy the cell B1 to clipboard, select the target range, i.e. Shift+Ctrl+T to focus the Name Box and input B2:B99 or whatever the last data row is and hit Enter, then paste. The relative references A1 will be adapted to A2, A3, … so for each row there’s a corresponding formula.

#5

Thanks again for the help. But I have one final clarification. I’m confused about how to re-format the resulting output into [HH]:MM:SS. Each time output gives a number, and is correctly added. But I don’t understand how to re-format the number into hours, minutes, and seconds. Here is a screen recording of what I do - https://streamable.com/msvmd

Can you explain again how to present the output in a [HH]:MM:SS time format?

#6

Hi. Is it possible to get an answer to the question above? @erAck are you there?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.