# Variable 5 day work week fomula help

#### cwcallen76

##### New Member
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.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### DonkeyOte

##### MrExcel MVP

+(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...

Last edited:

#### MickG

##### MrExcel MVP
Something like:-
Code:
``=NETWORKDAYS(TODAY(),TODAY()+INT((((E70)/(20))/(0.85))))``
Regards Mick

#### DonkeyOte

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

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

Thanks alot.

Replies
1
Views
102
Replies
0
Views
791
Replies
6
Views
580
Replies
3
Views
932
Replies
34
Views
276

1,191,693
Messages
5,988,139
Members
440,129
Latest member
bianca88

### 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.

### Which adblocker are you using?

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

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