Excel Magic Trick 1250: Add Times to Get Total Minutes & Seconds From Downloaded Music Data

Learn how to add times to show total minutes and seconds for copy and pasted downloaded music data. Learn about Time Math, Time Number Formatting and How to shows hours greater than 24 hours:
1. (00:11) Problem Introduction
2. (01:13) Learn about Time Number Formatting and the Time Values Excel stores under the Time Number Formatting
3. (03:15) See how standard Time Number Format ca NOT show hours greater than 24 hours
4. (04:09) Learn the Custom Time Number Formatting to show hours greater than 24 hours: [hh]:mm
5. (05:01) Learn how to Convert Hours To Minutes and Convert Minutes to Seconds
6. (06:03) Solution #1: Add times with SUM function and use the Custom Time Number Formatting: [hh]:mm. This solution displays the correct value, but the time value is in hours.
7. (06:34) Solution #2: Add times with SUM function and divided by 60 (=SUM(B2:B5)/60), then apply the Time Number Formatting: hh:mm:ss. This solution gives the correct time value.
8. (07:51) Solution #3: Add times with SUM function and use the Custom Time Number Formatting: [hh] “minutes” mm “second”. This solution displays the correct value, but the time value is in hours.
9. (09:02) Solution #4: Formula for actual minutes and whole numbers: =INT(SUM(B2:B5)*24)
10. (09:56) Solution #5: Formula for actual seconds and whole numbers: =MOD(SUM(B2:B5)*24,1)*60

[hildyfine]

THANK YOU from the bottom of my heart. Beautiful, clear explaining and demonstrating.

[PopFictionVideos]

Thank you for confirming that Microsoft completely dropped the ball with this (lack of a) feature. Seriously, an 11 minute confusing video is required to add up SIMPLE minutes and seconds??? You can do better Microsoft.

[urrkuh]

SO SO helpful!!!! thank you!! Wasn't able to find instructions as clear and concise as this one

[OvertEnemy]

Cool video but can't believe excel can't just do this automatically. Crazy.

[ExcelStrategy]

Super-refresher video on time management in Excel !

[bronze]

I am looking to at up simple minutes and seconds from recording and sometimes there is are recording over the hour. You started with the simple problem. I get it that you understand excel extremely well. I simply need to know what format I need for the data, and the formula I need to use to add these up. I am sure you have provided it in this video, however, I was totally overwhelmed and became lost. If you did this video showing the answer first and them the second part was the all the background formulas, I am sure many people would appreciate it being split into these 2 parts. Personally i do not want to know what excel is doing, i just want the formula so I can resolve this issue. I still do not know what to do.

[krn]

Wow. Thanks Mike. Great TIME explanation.

[rdsabd]

Very helpful! Awesome explanation. Thank you much!

[RosiePerera]

Thank you, Mike @excelisfun. This was so helpful. However there's one problem: I entered a bunch of track lengths as mm:ss (4:00, 12:35, 3:28, etc.) Excel formatted them as h:mm and they looked like I entered them; so far so good (sort of). But when I entered 25:22, it formatted it as [h]:mm:ss, which came out looking like 25:22:00, and was treated as 1:22 in h:mm formatting. So the math will come out wrong if I follow your instructions. This is a problem for any number of minutes > 23.

[christinelisi]

Hi. Thank you for this tip.What's missing, though, is how your LENGTH cells are formatted. I have a spreadsheet that shows the average minutes:seconds it takes on each call in our call center. I can't figure out how to get excel to see the number 9:40 as 9 minutes and 40 seconds. When I use TIME formatting it sees it as AM or PM. When I use CUSTOM mm:ss (with or without brackets) I still don't get the desired results. Can you help? Thank you so much!

[nasribrahim]

Hi great video,
sorry i have a question out of this video topic.
can we have rows to repeat at bottom of each printed page just like we do in the top?

[ExcelGuru]

please let me know where i can find 1200 emt series number please

[lazalazarevic]

r u going to make excel 2016 series soon? just on the good new stuff {I gues there are some :)}

[horowitzjohn]

JFC. Why is this simple calculation so complex?

[luckystrike]

i just don't get it. I need to calculate minutes and seconds
0:35 + 1:35 + 0:48 + 0:48 +0:10
when i put in [hh]:mm excell add 01/01/1900

[E_Crypto]

'SUM(XX:YY)/60' and custom format how we like. Problem solved

[HusseinKorish]

... no excel 1250 yet uploaded at the site ?!

[krdrtother]

Camtasia Studio will Zoom Pan until end video text displayed problem "Total of Underlying Number with Custom Time Number ...", "Total of Underlying Number with NO Number Formattin..." and "1 represents 24 hours and 0.484027778 represents (11+..." you have to cry texts. Excelisfun please.