Convert hours into weeks/days/hours

[jeffreybrown]

I have a follow-up question to the help Barry provided the other day, but a little different; therefore, a new thread.

http://www.mrexcel.com/forum/showthread.php?t=446168

I wanted to take what Barry provided and now turn it into weeks from a cell with straight hours, but this proves not so easy for me.

Cell K7 is the formula provided by Barry, but for what I am doing now I replaced the Sumproduct with Text(K6...etc). It seems to work, but it brings up another question.

If I have 8:00:00 in a cell and I format to a number it would be 0.333333333333333.

If I put .33 in a cell and format to [h]:mm:ss the answer is 7:55:12, so in the Text(K6) part I added a few more decimals to the format and I came up with 8:00:00 even. Is this the right way or am I going down the wrong path?

So how can I get the results of cell K8 off of the 145:00:00? It doesn't neccesarily have to be in the same format, whatever is easiest.

Excel Workbook
	K
6	145:00:00
7	18d 01:00:00
8	3w 3d 1h
1040 Roll-UP

 

[Matt Rogers]

Hi,

One week has 168 hrs.
(24*7)

180 hrs =
1 week, 0 days and 12 hours

 

[Lograf]

True. I guess I didn't explain that I was basing all this on a 40hr (8 hour 5 day work week) in which 180 hours would be 4 weeks, 2 day, & 4 hours. Trying to figure vacation time available.

 

[Matt Rogers]

Ok, maybe after dinner.
With an empty stomach I can't count ...

Matt

 

[Matt Rogers]

Try ...:

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='4' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='40,5pt'><col width='6pt'><col width='82,5pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >hours</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >180</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 2D 4hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >181</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 2D 5hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >182</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 2D 6hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >183</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 2D 7hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >184</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 0hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >185</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 1hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >186</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 2hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >187</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 3hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >188</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 4hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >189</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 5hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >190</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 6hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>13</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >191</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 3D 7hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>14</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >192</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >4W 4D 0hrs</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td><Span style='color:#222222'>=QUOTIENT</Span><Span style='color:#0000DD'>(A2,40)</Span><Span style='color:#222222'>&"W "&QUOTIENT</Span><Span style='color:#0000DD'>(A2,<Span style='color:#0000DD'>8</Span>)</Span><Span style='color:#222222'>-</Span><Span style='color:#0000DD'>(QUOTIENT</Span><Span style='color:#222222'>(A2,40)</Span><Span style='color:#0000DD'>*5)</Span><Span style='color:#222222'>&"D "&A2-</Span><Span style='color:#0000DD'>(8*QUOTIENT</Span><Span style='color:#222222'>(A2,<Span style='color:#222222'>8</Span>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>&"hrs"</Span></td></tr></table>

 

[Lograf]

Works great. Thank You !

 

[Matt Rogers]

You're welcome.

Addition to "normal" times:
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='8' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='51pt'><col width='4,5pt'><col width='75pt'><col width='51pt'><col width='51pt'><col width='51pt'><col width='51pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td><td align='middle'>F</td><td align='middle'>G</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >hours</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >W/D/hrs</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >Weeks</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >Days</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >hrs</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >180</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 12hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >12</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >181</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 13hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >13</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >182</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 14hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >14</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >183</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 15hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >15</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >184</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 16hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >16</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >185</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 17hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >17</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >186</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 18hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >18</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >187</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 19hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >19</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >188</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 20hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >20</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >189</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 21hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >21</td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >190</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 22hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >22</td></tr><tr><td style='background-color:#cacaca' align='middle'>13</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >191</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 0D 23hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >23</td></tr><tr><td style='background-color:#cacaca' align='middle'>14</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >192</td><td align='middle' style='font-family:Calibri; font-size:11px; ' > </td><td align='right' style='font-family:Calibri; font-size:11px; ' >1W 1D 0hrs</td><td align='right' style='font-family:Calibri; font-size:11px; ' > </td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >1</td><td align='middle' style='font-family:Calibri; font-size:11px; ' >0</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td><Span style='color:#222222'>=QUOTIENT</Span><Span style='color:#0000DD'>(A2,16<Span style='color:#0000DD'>8</Span>)</Span><Span style='color:#222222'>&"W "&QUOTIENT</Span><Span style='color:#0000DD'>(A2,24)</Span><Span style='color:#222222'>-</Span><Span style='color:#0000DD'>(QUOTIENT</Span><Span style='color:#222222'>(A2,16<Span style='color:#222222'>8</Span>)</Span><Span style='color:#0000DD'>*7)</Span><Span style='color:#222222'>&"D "&A2-</Span><Span style='color:#0000DD'>(24*QUOTIENT</Span><Span style='color:#222222'>(A2,24)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>&"hrs"</Span></td></tr><tr><td>E2</td><td><Span style='color:#222222'>=QUOTIENT</Span><Span style='color:#0000DD'>(A2,16<Span style='color:#0000DD'>8</Span>)</Span><Span style='color:#222222'></Span></td></tr><tr><td>F2</td><td><Span style='color:#222222'>=QUOTIENT</Span><Span style='color:#0000DD'>(A2,24)</Span><Span style='color:#222222'>-</Span><Span style='color:#0000DD'>(QUOTIENT</Span><Span style='color:#222222'>(A2,16<Span style='color:#222222'>8</Span>)</Span><Span style='color:#0000DD'>*7)</Span><Span style='color:#222222'></Span></td></tr><tr><td>G2</td><td><Span style='color:#222222'>=A2-</Span><Span style='color:#0000DD'>(24*QUOTIENT</Span><Span style='color:#222222'>(A2,24)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'></Span></td></tr></table>

 

[barry houdini]

When I put "180" in the B1 cell it calculates 4w 2d 96h and it should be 4w 2d 4h

Yeah, sorry, I messed that up - my version should be like this:

=TEXT(INT(B1/40),"0w ;;")&INT(MOD(B1,40)/8)&"d "&MOD(B1,8)&" h"

One possible advantage is that the weeks aren't shown if they are zero, so if B1 is 23 the result is just "2d 7h"

 

[Lograf]

Thanks. I appreciate the help.