Counting Business Days vs. Calendar Days

[RickGreg]

I have a calculation that generates the number of hours required to complete
an industrial operation. We now want to express the duration in terms of
elapsed calendar days, and the facility does not operate on weekends.

For example:

....If the operation takes less than 5 days, it is simply [total hours/24].

....If the operation takes more than 5 days, we need to add 2 weekend days.

....If the operation takes more than 10 days, we need to add 4 weekend days.

And so on...

Can anyone suggest a simple way to do this??

My only ideas so far are (a) a long nested IF equation, or (b) a lookup
table (I don't think we'd ever exceed one year, so 52 entries in the table
would do it!)

(Using Excel 2004 v11.2)

Many thanks!

 

[JE McGimpsey]

My only ideas so far are (a) a long nested IF equation, or (b) a lookup
table (I don't think we'd ever exceed one year, so 52 entries in the table
would do it!)

if total hours/24 is used for less than 5 days, it seems to me that this
should work:

=total_hours/24 + 2*INT((total_hours-0.000001)/120)

 

[RickGreg]

Thank you. That does seem to work.

I'm curious why: (total_hours-0.000001) is necessary. Why not simply:
INT(total_hours/120)??

I'm guessing it's so a period that is a multiple of 5 days (e.g, 120 hrs,
240 hrs, etc) does not get an extra weekend added. Correct?

Again, thanks for your help!

-Rick

 

[JE McGimpsey]

I'm guessing it's so a period that is a multiple of 5 days (e.g, 120 hrs,
240 hrs, etc) does not get an extra weekend added. Correct?

Precisely.