converting cumulative time to workweek time

[cclinton]

I have a chart that contains time per task and then adds the tasks for a grant time total. Example:
0:05 wash hands
0:10 brush teeth
0:20 shave
0:35 - total morning prep time

In my chart the total time is currently 63:25:00 or
2d 15h 25m

I want to break this down into a number that can be compared to a work week. In other words, if a typical work day is 8 hours, can I break this down to show work days and remaining hours and minutes? I'd like it to break down as:
?days ?hours ? minutes
where days is an 8 hour day, not a 24 hour day

Thanks.

 

[Misca]

Time is just numbers so it's quite straight forward:

Excel Workbook
	A	B	C	D
1	Total Hours	Hours / Working day	Working Days	Hours & Minutes
2	63:25:00	8:00:00	7	7:25:00
Sheet

 

[BROWN_RY]

One way, if you space your day/hr/min out into three cells and convert all of your numbers into hours:

A1, B1, C1
2 15 25
=a2*24 =b2 =c2/60
48 15 .41667

You sum those values and get 63.41667 (in cell D2) total hours.

Then divide 63.41667/8 = 7.9333 workdays in cell D3
Then in D4 put equation =right(d3,len(d3)-1)*8
In D5 put =right(d4,len(d4)-1)

In cell A4 put equation =rounddown(D3,1) = 7 days
In cell b4 put equation =rounddown(d4,1) = 7 hours
In cell c4 put equation =d5*60

Now a4, b4, c4 should read 7 work days, 7 hours, 25 mins.

hope that helps

 

[cclinton]

Thanks.
Brown RY - I don't think I am able to split them into separate cells for hours, minutes and seconds.
Misca - is there a way to create a formula that doesn't require me to create a cell that includes the 8:00:00? I have tried (using your cell example):
=A2/8
=A2/8:00:00
In both cases I get strange results. I have even tried changing formatting the cell to show accounting versus general and then I get all sorts of strange numbers.

 

[Misca]

8 hours = 1/3 days (3 x 8 h = 24 h = 1 day) so replace the 8 hours with 1/3 ( = multiply the original hours by 3) and you'll shouldn't need the helper cell.

I also forgot to mention the working days cell was formatted to hide the decimals.

 

[cclinton]

thank you very much.