Excel 2007 : How to convert total number of Hours into working days

[Bazzzer]

Hi I have with the communities help suceeded to build an elapsed business time calculator, the final part is to split the resulting hh:mm:ss into dd hh:mm:ss...
Taking into account that the business working day is 8hrs....
The example is: Total time is 20hrs 14 mins 18secs which equates to 2 working days 4hrs 14mins and 18secs.
Again I am sure that there is answer somewhere....?
Thanks in advance
Bazzzer

[DonkeyOte]

As long as duration never exceeds 32 working days you can use:

B1: =INT(A1/"8:00")+(MOD(A1/"8:00",1)*8/24)
where A1 holds duration of 20:14:18

and apply custom format to B1 of: d "working days "h"hrs "m"mins and "s"secs"

If duration can exceed 32 working days then you will need to build a literal string (rather than storing result as a decimal day value)

B1: =INT(A1/"8:00")&" working days "&TEXT(MOD(A1/"8:00",1)*8/24,"h""hrs ""m""mins and ""s""secs""")

[Bazzzer]

Again I am so greatfull

many thanks thsi is just what I wanted
Bazzzer

[tanzpunz]

Hi guys, sorry to reopen the thread. I have a very similar problem but using the formula proposed my Excel gives me a syntax error:
=INT(L2/"8:00")+(MOD(L2/"8:00",1)*8/24) on the part in bold of the formula.
I use the italian version of Excel 2007, but it accept the english formula too, I even tried to used the ita formula called RESTO and it gaves me the same error.

I give you a resume about what i need and I tried:
cell L2 (formatted [hh]:mm) shows the sum (correctly) about hours:minutes of worked hours.
cell L1 I cannot succed in having as a result the worked days on the 8 hours/day using the value in L2. I tried both in date and decimal format.

Ex.1: 38:00 hours into L2. I tried with the formula =INT(L2/(ORARIO(8;8;0))) too and it gives 4 (while the correct in decimal is 4.75) with the cell in number format, while it gives 5 if in format "dd".

Ex.2 I tried to leave L1 in format [hh]:mm and divide L2/8. It gives 04:45 (equal to the correct decimal 4,75 result).

But the correct result I expect is to see 4d:6h.

Pls. help me I'm gonna crazy about that!
Thanks you all for the patience and the help!