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

 

[barry houdini]

You shouldn't really need TEXT function for K6 in that formula, you can just use K6 on it's own. To show weeks, days and hours try

=TEXT(INT(K6/"40:00"),"0""w "";;")&INT(MOD(K6,"40:00")/"8:00")&"d "&ROUND(MOD(K6,"8:00")*24,0)&"h"

 

[jeffreybrown]

Thanks Barry, as usual this is perfect...

 

[texasalynn]

edit: doh - too slow
Barry got it

 

[jeffreybrown]

Hi Barry,

With this formula you provided, I tried to figure out how to get it to show the minutes also, but the best I could do is

=TEXT(INT(M3/"40:00"),"0""w "";;")&INT(MOD(M3,"40:00")/"8:00")&"d "&TEXT(MOD(M3,"8:00")*24,"0.00")&"h"

but this displays 3w 3d 1.58h for 145:35:00

any thoughts to display 3w 3d 1h 35m

 

[barry houdini]

Try this version

=TEXT(INT(M3/"40:00"),"0""w "";;")&INT(MOD(M3,"40:00")/"8:00")&"d "&TEXT(MOD(M3,"8:00"),"h""h ""mm""m""")

 

[jeffreybrown]

Thanks Barry works great

 

[Lograf]

You shouldn't really need TEXT function for K6 in that formula, you can just use K6 on it's own. To show weeks, days and hours try

=TEXT(INT(K6/"40:00"),"0""w "";;")&INT(MOD(K6,"40:00")/"8:00")&"d "&ROUND(MOD(K6,"8:00")*24,0)&"h"

I am trying to create similar conversion without any success. I want to convert hours expressed as an integer (i.e. 180 hours) to weeks, days, & hours.

For example 180 hours (in cell B1) =4 weeks, 2 day, & 4 hours.

Any help would be great as I am not an experienced Excel user.

 

[barry houdini]

Hello Lograf, welcome to MrExcel

Try this version

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

 

[Lograf]

It doesn't calculate the hours correctly. I did change the K6 to B1 in the hours part of the equation. When I put "180" in the B1 cell it calculates 4w 2d 96h and it should be 4w 2d 4h