Networkdays Formula

**Madball** · 01-31-2008, 06:41 AM

Hi,

Okay currently I'm working as project manage for a software development studio and I am using MS project for all my work. Since the development team aren't aloud to have a MS project license on their PC I have to copy their tasks from MS project and put them into Excel.

I would say that I am okay using Excel but not an expert. I want to create a formula that uses NETWORKDAYS to determines a finish date from a duration and start date.

The headers I would use for each column are Duration, Start and Finish. So I would like to be able to get a team member to put a duration down i.e 1 day and give me the start day and in the finish date it produces a finish date automatically without including Bank Holidays and Weekends. What would the formula be for that?

Any help will be great

Thanks

**davesexcel** · 01-31-2008, 07:33 AM

Hi,

This is copied right from excels help,
use the function wizard, select networkdays, at the bottom of the form will be Help on this function
A B
Date Description
10/01/2008 Start date of project
3/01/2009 End date of project
11/26/2008 Holiday
12/4/2008 Holiday
1/21/2009 Holiday
Formula Description (Result)
=NETWORKDAYS(A2,A3) Number of workdays between the start and end date above (108)
=NETWORKDAYS(A2,A3,A4) Number of workdays between the start and end date above, excluding the first holiday (107)
=NETWORKDAYS(A2,A3,A4:A6) Number of workdays between the start and end date above, excluding every holiday above (105)

Note To convert the range of cells used for holidays in the last example into an array constant, select reference A4:A6 in the formula and then press F9.

**Madball** · 01-31-2008, 07:38 AM

Thanks for that, but I think that will not work, the problem I have is we have a start date for a project we are working on that is 31st Jan for example, I've tasks the team members to give me tasks they need to do to complete their side of the project and durations, i.e 1 day, 2 days, 0.5 days.

So I know the start date and how long each task is roughly going to take, but I do not know the finish date of each task, for example if a task was meant to take 2 days but ends up taking 3 days from the 31st of jan I want it to update the finish date accordingly?

Does this make any sense lol.

**davesexcel** · 01-31-2008, 08:38 AM

Okay,
If you have the start date in A1,
enter a number of days in B1
C1 will formulate A1+B1, the answer being a workday
Is this correct?

**Madball** · 01-31-2008, 09:03 AM

Okay,

I've attached a file for you to look at with a formula (not using networkdays).

I hope this helps :D

**arthurbr** · 01-31-2008, 09:27 AM

If you have the Analysis Tool Pak installed, use the WORKDAY function

**Madball** · 01-31-2008, 10:31 AM

Hi,

Yes this is what I've been trying to do, but I'm not entirely sure how the Workday function works doesn't it work if you have 2 dates and it gives you a duration, How do I get it to work using 1 date and a number for example 2 days and it displaying a finish date?

**arthurbr** · 01-31-2008, 10:37 AM

The WORKDAY function ( which is different from the NETWORKDAY function) uses :
start date
nr of days
eventually holidays
It will give you the finish date weekends ( and eventually holidays )not included

**Madball** · 01-31-2008, 10:46 AM

Ah sorry I miss read it, okay I have got it working, but I think there might be a problem with it. Some of the tasks that were given to me are 1/2 a day and when I use the Workday function it doesn't seem to understand half days so the finish date is constantly the same for anything with half a day.

For Example I have 5 tasks all saying they are half a day, and the finish date is all the same which is the 4th Feb. Is there any way of correcting this?

**arthurbr** · 01-31-2008, 10:55 AM

Say the start date is in A, task duration in B1 you could use :
=if(b1<1,workday(a1,1),workday(a1,b1))

**Madball** · 01-31-2008, 11:10 AM

okay it is working but I think it is me that is doing it wrong for sure.

For example I have a Start date in A1 which is using the Formula =TODAY() to get the date.

In B1 I'm just entering a number, lets say 0.5.

In C1 I have your formula and it works :D

Now here is where I think I am going wrong.

In B2 I want it to look at the finish date and then produce a start date so at the moment I just have it using this formula =C1, which works fine if you are using whole numbers, but as soon as you use 0.5 it stops working.

Is there anyway around this?

Also if you want me to attach the document I am using I can so you can fully understand where I'm coming from?

**arthurbr** · 01-31-2008, 11:19 AM

use =ceiling(value,1)
This will leave integer values intact and round decimals to the next number

**Madball** · 01-31-2008, 11:24 AM

Hi again,

Sorry to be a complete pain in the butt, but I tried that CEILING function and it doesn't seem to work.

I've attached the test file I am using with both formulas you've asked me to use to see if you can see where I am going wrong.

**Madball** · 02-01-2008, 06:14 AM

okay after an evening of messing around I've managed to figure it to work with half days, the only problem I have now is trying to get it working with Holidays and if someone takes half a day off for Holiday.

Please find attached the file I created the formula, can anyone help me with the holiday part?