adding seconds and minutes to get hours and days

[sparkspll]

I am trying to add duration time for song files. 4 minutes and 32 seconds + 3 minutes 45 seconds, etc. etc. to get total hours and then to total to days. The range of cells is over 5,000 and I am trying to find out how long a playlist will play.
Thank You in Advance, sparkspll

 

[Jon von der Heyden]

Hi

Sorry I can't receive your spreadsheet, but I imagine one of the two suggestions here should apply to your scenario.

Book4
	A	B	C	D
1	Time (MM:SS)		Minutes	Seconds
2	11:31		11	31
3	47:31		47	31
4	57:36		57	36
5	24:29		24	29
6	41:46		41	46
7	27:22		27	22
8	15:50		15	50
9	02:53		2	53
10	27:22		27	22
11	08:38		8	38
12	23:02		23	2
13
14	0 days and 4:48:00		282	360
15
16	60	seconds per minute	0	days
17	3600	seconds per hour	4	hours
18	86400	seconds per day	48	minutes
19			0	seconds
Sheet1

If you have a column containing time that is formatted as time then a simple SUM() of the column with appropriate format could do the job (but notice Barry's answer above if the total exceeds 31).

Or if the elements are segmented into different columns (in my example C:D) then this can be formulated in e.g. these steps:

C16: DAYS =INT(((C14*60)+D14)/86400)
C17: HOURS =INT(((C14*60)+D14)/3600)-(C16*24)
C18: MINUTES =INT((((C14*60)+D14)/60)-(C17*60))
C19: SECONDS =((C14*60)+D14)-(C18*60)-(C17*3600)-(C16*86400)

Not sure if this helps but I hope it does

 

[sparkspll]

WOW!! Great help this is exactly what I wanted to do. Most Appreciated. Thanx Again, sparkspll

I do also have a problem adding KiloBytes, example below. If you could help with matter as well, Thanx
song 1 = 9,541kb
song 2 = 7,451kb
etc.
I really don't need to add to mb just straight to gb as my list is large.
Thanx in advance once again, sparkspll

 

[sparkspll]

I think I figured out the problem I was having with html maker. Below is a sample of formulas I am still having trouble with. The cells with the ? marks. Thanx again, sparkspll

Song Size & Time.xls
	E	F	G	H	I	J	K
1	TotalGigaBytes>		?	TotalHours>	?	TotalDays>	?
2	KiloBytes	Minutes	Seconds
3	9,961	4	14
4	3,181	3	21
5	4,265	1	49
6	5,039	2	0.08
7
Sheet1

 

[Jon von der Heyden]

Using your snap shot:

G1: =SUM(E3:E6)/1048576
I1: =INT(((SUM(F3:F6)*60)+SUM(G3:G6))/3600)-(K1*24)
K1: =INT(((SUM(F3:F6)*60)+SUM(G3:G6))/86400)

Kind regards
Jon

 

[sparkspll]

Thank You so much for your much appreciated help, the formula's are working perfectly.
Much Thanx, sparkspll