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 Sparkspll,

It would be useful to know the format of your data, perhaps even a sample using Colo's HTML Maker.

For isntance, are you mins:secs in time format? If they are you could simply SUM() the range and change the format of the summation to dd:mm:ss.

If not then I think you need to describe how the minutes and seconds are captured.

Regards
Jon

 

[sparkspll]

song 1 3:05
song 2 4:23
song 3 6:40
song 4 11:42
I would like to enter these numbers to total out to hours then after 24 total out to days, Thanx again in advance, sparkspll

 

[Richard Schollar]

Hi

Are the song names (eg "song1" etc) in separate cells to the times ("3:05") or the same cells?

As the Baron said, it would help if you posted a sample of the actual data.

You can add times like so:

Book2
	A	B	C	D
1	Song1	0:04:05
2	Song2	0:02:54
3	Song3	0:01:59
4		0:08:58
5	ifinasinglecell:
6	Song14:05	0:04:05
7
8	ifintwocellsbuttextformatted:
9	Song1	4:05	0:04:05
Sheet1

Formula in B6 is:

=("0:"&REPLACE(A6,1,FIND(" ",A6),""))+0

in C9 is:

=("0:"&B9)+0

 

[sparkspll]

I tried the html maker and had security issues. Yes songs are in one column, format in another then size in mb, then minutes, then seconds. all in seperate columns, I would like to add the minutes and seconds to get hours and then convert the total to days.
Sorry I couldn't get the maker to work to show an example, hope this helps a little more, Thanx again, sparkspll

 

[Richard Schollar]

Given minutes in column B and seconds in column C then say in column D you can convert to time thus:

=(B1/1440)+(C1/86400)

Format as Time (Format>Cells>Custom and type in [h]:mm:ss).

You can then sum these columns together. This won't give a total in days - but you can use a custom format (again) to provide this such as:

d "days and " h:mm:ss

 

[sparkspll]

Thanx for your reply, I have minutes and seconds adding correctly but I am a little confused about the formula for days. After putting in some test data for minutes and seconds I am not sure of the proper formula to convert it to 24 hours and add one day and start totaling for the next.
Again Thanx sparkspll

 

[Jon von der Heyden]

Hi sparkspll

Richards solution does not require extra formulation for days output. Say your data goes from row 1 to 100. You will have applied Richards formula in column D then =SUM(D2:D100) will return an output. With this cell selected go to Format->Cells. Make sure you are on the Number Tab and go to Custom. Then apply the format code Richard provided:d "days and " h:mm:ss in the "Type" text box.

Hope this helps.

 

[barry houdini]

In you're original post you say you want

"....to get total hours and then to total to days"

Do you want just one calculation showing days and hours or two separate results?

You have to be a little careful with the suggested custom format

d "days and " h:mm:ss

as this won't show any days figure higher than 31

 

[sparkspll]

Thank You all for your patience, I can not get HTML Maker to work for some reason. Is it possible to send a sample spreadsheet to someone who can (let's say) fill in the blanks for me. Would be most appreciated.
Thanx Againg, sparkspll