Cannot Sum Days, Hours and Minutes

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
86
Hi,

What is the best way to sum the below values up to get a total?

D2: 21 days 17 hours 44 minutes
D3: 13 days 01 hours 43 minutes
D4: 22 days 02 hours 30 minutes

The format of these cells are d "days" hh "hours" mm "minutes"

Thanks.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Marvin,

I don't know if it's the best way but it's a way...

Extract the number of minutes from each row into a new column, SUM those minutes then extract the SUM back into the text version.

DE
1PeriodMinutes
221 days 17 hours 44 minutes31304
313 days 01 hours 43 minutes18823
422 days 02 hours 30 minutes31830
556 days 21 hours 57 minutes81957

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
marvin jones

Worksheet Formulas
CellFormula
E2
to E4
=(LEFT(D2,SEARCH("days",D2)-1)*1440)+(MID(D2,SEARCH("days",D2)+4,SEARCH("hours",D2)-(SEARCH("days",D2)+4))*60)+MID(D2,SEARCH("hours",D2)+5,SEARCH("minutes",D2)-(SEARCH("hours",D2)+5))
D5
=INT(E5/1440)&" days "&(INT(MOD(E5,1440)/60))&" hours "&INT(MOD(MOD(E5,1440),60))&" minutes"
E5=SUM(E2:E4)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
The trick isn't adding the values, but making sure the formatting displays the days part. Exceeding 31 days in time/date formatting restarts due the months!
Even your sample is subject to this potential problem.
 
Upvote 0
Hi Toadstool,

Thanks for the reply, although i'm getting N/A values in cells E2 to E4. Should i have these cells in text?

Thanks.
 
Upvote 0
Marvin,

I don't know if it's the best way but it's a way...

Extract the number of minutes from each row into a new column, SUM those minutes then extract the SUM back into the text version.

DE
1PeriodMinutes
221 days 17 hours 44 minutes31304
313 days 01 hours 43 minutes18823
422 days 02 hours 30 minutes31830
556 days 21 hours 57 minutes81957

<tbody>
</tbody>
marvin jones

Worksheet Formulas
CellFormula
E2
to E4
=(LEFT(D2,SEARCH("days",D2)-1)*1440)+(MID(D2,SEARCH("days",D2)+4,SEARCH("hours",D2)-(SEARCH("days",D2)+4))*60)+MID(D2,SEARCH("hours",D2)+5,SEARCH("minutes",D2)-(SEARCH("hours",D2)+5))
D5=INT(E5/1440)&" days "&(INT(MOD(E5,1440)/60))&" hours "&INT(MOD(MOD(E5,1440),60))&" minutes"
E5=SUM(E2:E4)

<tbody>
</tbody>

<tbody>
</tbody>

Ah - its works now - as my source data is from a pivot table, this was topping it!
 
Upvote 0
You can evaluate the results to text.
Code:
=INT(A1)+0&" Days " & TEXT(MOD(A1,1),"h") & " Hours and " & TEXT(MOD(A1,1),"m") & " Minutes"
Where A1 would be the the SUM() of time values
 
Upvote 0
Hi SpillerBD,

Ah yes, this works great, thank you.

The source data comes from a pivot and the total in days, etc, will be going over 31 soon. I'm going to convert the individual times that make up the total into number format and then convert it using your formula. Will this work OK? It seems to when i tried it!

Thank you for your help :)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top