Variable 5 day work week fomula help

[cwcallen76]

I am using Excel 03 with Vista OS. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I work for a machine shop and I am trying to find a formula that will calculate a date within a 5 day work week. My current formula is:<o></o>
<o></o>
=TODAY()+(((A1)/(20))/(0.85))<o></o>
<o></o>
Saying A1 = hours left to complete a project, we work for 20 hours a day at 85% efficiency, and work 5 day a week. <o></o>
<o></o>
Is there a way to find the correct completion date? Currently it gives me a good date, but I have to manually go in and add the weekends and holidays.

 

[DonkeyOte]

could you add the below to your formula?

+(FLOOR(((A1/20)/0.85)/5,1)*2)

This takes your result divides by 5 and rounds down to nearest whole number -- ie 5.1/5 = 1

then multiplies result by 2 to add weekend days.

so if it were 10.5 days it would give you 4 weekend days to add to your original date...

not sure what approach you're using for roundings...

EDIT: Sorry, this doesn't work does it... if you have a 3 day completion period and 1 working day remaining...

 

[MickG]

Hi,What about "NetworkDays" Function.
Something like:-

=NETWORKDAYS(TODAY(),TODAY()+INT((((E70)/(20))/(0.85))))

Regards Mick

 

[DonkeyOte]

continuing the networkdays theme... I think this would work ?

=TODAY()+((A1/20)/0.85)+(CEILING((A1/20)/0.85,1)-NETWORKDAYS(TODAY(),TODAY()+((A1/20)/0.85)))

 

[cwcallen76]

I tried all the formulas posted. the last one
=TODAY()+((A1/20)/0.85)+(CEILING((A1/20)/0.85,1)-NETWORKDAYS(TODAY(),TODAY()+((A1/20)/0.85)))
this formula is still not calculating out the weekends in the final result.

I.E. say a1=135 hours the result would be 8 days when rounded up.

8 workdays from today (6-11-08) including today is 6-19-08. it results in the 20th. Also if you reduce the 0.85 to 0.70 it results in 6-22-08 which is a weekend day.

the formula:
=NETWORKDAYS(TODAY(),TODAY()+INT((((E70)/(20))/(0.85))))

resulted in 1/6/1900

thanks in advance for the assistance.

 

[DonkeyOte]

hmm... just maybe we're making this a little more difficult than it need be ???

=WORKDAY(TODAY(),(A1/B1)/C1)

where A1 = hours to work, B1 = hours per day and C1 = utilisation/efficiency %

Not checked but I suspect WORKDAY function requires Analysis ToolPak Add-in installed.

 

[cwcallen76]

Eureka......That did it. Some times we try to make it too difficult.

Thanks alot.