Convert hours into weeks/days/hours

jeffreybrown

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,152
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
6145:00:00
718d 01:00:00
83w 3d 1h
1040 Roll-UP
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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"
 
Upvote 0
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
 
Upvote 0
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""")
 
Upvote 0
barry houdini said:
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"
Click to expand...


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.
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
I can't sum up hours anymore
Replies
7
Views
282
awoohaw
awoohaw
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
697
Asbestos_Jen
Asbestos_Jen
K
  • Solved
How to sum up working hours (a different schedule)
Replies
9
Views
460
KarolinaKaralaite
K
A
  • Question Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
965
Aadya
A
D
  • Solved
VLOOKUP or INDEX/MATCH utilizing day of the week
Replies
5
Views
608
Darren_workforce
D

Forum statistics

Threads
1,225,149
Messages
6,183,186
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top