I have this time periods and want to see the total duration of all cells. I used sum(d3:d100) , but it does not work.
I did format entire column of periods as time.
1 Answer
(As I put here: )
Between your question and your comments, I couldn't figure out if you are trying to count hours and minutes, or minutes and seconds. The solution works in either case.
I drafted an explanation with both possibilities, just in case. (Unfortunately, I don't have enough rep to post images, so that's the link to the image.)
Here's an explanation if you can't get to the image for whatever reason:
By default when you SUM time in Excel, it counts time of day + time of day. For instance, 9:00 AM + 11:00 AM would be displayed as 8:00 PM.
What we want is to count AMOUNT of time. In that case 9:00 + 11:00 would be displayed as 20:00.
When counting minutes and seconds, enter the info as 00:mm:ss and format it as mm:ss. In the cell you calculate the SUM in, format it as [mm]:ss to count AMOUNT of time rather than TIME itself.
When counting hours and minutes, enter the info as hh:mm:00 and format it as hh:mm. In the cell you calculate the SUM in, format it as [hh]:mm to count AMOUNT of time rather than TIME itself.
If you wanted, you could also count days.
dd "days" hh "hours and" mm "minutes"
