JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Convert hours into weeks/days/hours
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.
Hi,
Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
Ok, maybe after dinner.
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 ""IENT</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>
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 ""IENT</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>
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"
Thanks. I appreciate the help.
Threads
1,214,848
Messages
6,121,914
Members
449,054
Latest member
luca142
- my version should be like this:
