CODEWITHSUNDEEP
excelms-office
Chandra
Chandra

Reputation: 166

How to calculate total time duration from individual durations

I am unable to calculate total time duration from individual durations

I have already tried below formats

1.) h:mm

2.) [h]:mm

3.) [h]:mm;@

Below are my excel time duration values

0:09
5:54
1:55
0:16
0:05
0:04
0:52
0:45
6:29
3:08
2:40
0:00
0:01
1:27
0:02
0:09
0:06
0:01
2:36
0:00
5:23
3:15
3:01
2:02

Upvotes: 0

Views: 284

Answers (2)

Mikku
Mikku

Reputation: 6654

Assuming the Data is Like for the second value 5:54 as 5 Hours 54 Minutes...

The Total is Coming out to be 40 Hours 20 minutes. For this you need to apply the format as : dd:hh:mm Which will give you complete day for 24 hours and 16 hours for hh and 20 mm for minutes. That would be your 40 Hours 20 Minues in total.

You can do as per the screenshot also to get the Hours & Minutes. Then perform the conversion to get the result.

enter image description here

Upvotes: 1

JvdV
JvdV

Reputation: 75840

You can use SUM() on your range. While that gives 16:20 as output, format that cell with custom format [hh]:mm.

So using =SUM(A1:A24) with custom format [hh]:mm on B1 returns 40:20

enter image description here

If your values are actually textvalues looking like timevalues this will return 00:00 as per your comment.

To overcome this issue, use formula: =SUM(TIMEVALUE(A1:A24)) and confirm through CtrlShiftEnter and keep the same format as mentioned above, [hh]:mm.

Upvotes: 0

Related Questions