NETWORK days addition?

[Ikrion]

I have a workbook that calculates turnaround time. So, the project starts on 01/01/2011(cell H8), and finishes on 02/15/2011(cell AO8). I use the function "=NETWORKDAYS(H8,AO8)-1" and it gives me the number of days. Seems to work great.

Well, now we need to estimate the project completion date, based off the number of workdays in between. So, a series of cells calculate how many days the project should take (cell L8), and once we put in the project start date (cell H8), we want it to calculate, based off workdays, the estimated completion date.

Any ideas?

 

[VoG]

Try

=WORKDAY(StartDate,Days,Holidays)

 

[Ikrion]

Ok. So, if:

H8 = Project Start Date
L8 = Number of days it should take
AO8 = Actual finish date (manually typed in when completed)
AN8 = Estimated date (calculated from H8 and L8, using workdays. This is the value I need to calculate but can't figure out)

To encorporate that into the WORKDAY fucntion, would I list it like this: ?

AN8 =WORKDAY(H8,L8,Holidays)


Thanks!

 

[Ikrion]

When I try and put that in, as I described above, I get a NAME? error on the cell. If I take out the word "Holidays" then the number is like 40,000. I do have the "analysis" add in activated.

 

[T. Valko]

When I try and put that in, as I described above, I get a NAME? error on the cell. If I take out the word "Holidays" then the number is like 40,000. I do have the "analysis" add in activated.

Holidays refers to a list or a named range of cells that hold holiday dates that are to be excluded from the calculation.

That 40,000 number, that's your result, just format that as Date.

Here's a simplified example...

Start date is 7/1/2011
Estimated days to complete is 1

A1 = Start date 7/1/2011
B1 = 1

=WORKDAY(A1,B1)

Returns 40728

After formatting the formula cell as Date we then get the date 7/4/2011.

However, the 4th of July is a national holiday and we won't be working that day (which just happens to be a Monday) so we need to exclude that date from the calculation.

So, let's put that date in a cell, say cell J1.

J1 = 7/4/2011

Then the formula becomes:

=WORKDAY(A1,B1,J1)

And now, the result of the formula when formatted as Date is 7/5/2011.

Cell J1 is the "holidays" list. The list can be as many cells as is needed:

J1 = 7/4/2011
J2 = 12/24/2011
J3 = 12/25/2011

=WORKDAY(A1,B1,J1:J3)

You can even give the list of dates a defined name like Holidays. Then:

=WORKDAY(A1,B1,Holidays)

 

[Ikrion]

You were right; I should have thought of defining the cell as a date.

Works like a charm. Thanks!!

 

[T. Valko]

You were right; I should have thought of defining the cell as a date.

Works like a charm. Thanks!!

You're welcome. Thanks for the feedback!