Minutes to Hours...Hours to Days

[theboyscout]

What I'm trying to do is convert sum of minutes and if over 59 into hours and if over 7 hours into a day. For example

[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Days
[/TD]
[TD]Hours
[/TD]
[TD]Minutes
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]9
[/TD]
[TD]8
[/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD]Desired
[/TD]
[TD]10
[/TD]
[TD]7
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
So, if(sum(D3,D4)>59,Mod(sum(D2,D3),60),sum(D3,D4))

And if(D4 > 59, (sum(C2,C3) + 1),

sum(C2,C3))​

<strike></strike>

And if(C4 > 7,

(sum(B2,B3) + 1), sum(B2,B3))​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[theboyscout]

You're correct...I mistakenly placed the 6 in hours instead of days. If you can could you create the subtraction formula?

 

[Eric W]

OK, try:

	A	B	C	D	E	F	G	H	I	J
Days	Hours	Minutes	Days	Hours	Minutes
48
Add	Subtract
Result	Result

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]49[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]48[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=SUM(B2:B3)+INT((SUM(C2:C3)+INT(SUM(D2:D3)/60))/7)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=MOD(SUM(C2:C3)+INT(SUM(D2:D3)/60),7)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=MOD(SUM(D2:D3),60)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=B2+B3+(C2+C3+(D2+D3)/60>=7)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=MOD(C2+C3+(D2+D3>=60),7)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6[/TH]
[TD="align: left"]=MOD(D2+D3,60)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=G2-G3-(H2*60+I2< H3*60+I3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=MOD(H2-H3-(I2< I3),7)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=MOD(I2-I3,60)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Columns A:D are how to add the values based on a 7-hour per day system, F:I is how to subtract. The B5:D5 formulas are for when you want to add more than 2 sets of numbers (just change the ranges). But if you are only adding 2 sets of numbers, the A6:D6 formulas are a bit shorter.

 

[theboyscout]

Eric...great work. Thanks