Cannot Sum Days, Hours and Minutes

[marvin jones]

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.

 

[Toadstool]

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.

	D	E
Period
21 days 17 hours 44 minutes
13 days 01 hours 43 minutes
22 days 02 hours 30 minutes
56 days 21 hours 57 minutes

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Minutes[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]31304[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]18823[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]31830[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]81957[/TD]

</tbody>

marvin jones​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2
to E4
[/TH]
[TD="align: left"]=(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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5
[/TH]
[TD="align: left"]=INT(E5/1440)&" days "&(INT(MOD(E5,1440)/60))&" hours "&INT(MOD(MOD(E5,1440),60))&" minutes"[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=SUM(E2:E4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

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.

 

[marvin jones]

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.

 

[marvin jones]

Hi SpillerBD,

I know, is there a way around this?

Thanks.

 

[marvin jones]

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.

	D	E
Period
21 days 17 hours 44 minutes
13 days 01 hours 43 minutes
22 days 02 hours 30 minutes
56 days 21 hours 57 minutes

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Minutes[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]31304[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]18823[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]31830[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]81957[/TD]

</tbody>

marvin jones​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2
to E4[/TH]
[TD="align: left"]=(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))[/TD]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=INT(E5/1440)&" days "&(INT(MOD(E5,1440)/60))&" hours "&INT(MOD(MOD(E5,1440),60))&" minutes"[/TD]
[/TR]
[TR]
[TH]E5[/TH]
[TD="align: left"]=SUM(E2:E4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Ah - its works now - as my source data is from a pivot table, this was topping it!

 

[SpillerBD]

You can evaluate the results to text.

=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

 

[marvin jones]

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