Networkdays formula

**JezLisle** · 11-02-2010, 08:12 AM

I have an issue where I need to workout the number of days between a set of dates. Now using just the Networkdays formula isnt any good as I need to determine if both the dates are falling into the correct week I want to report on. I have used this formulas below, but they are cumbersome and prone to crashing excel, is there an easier way that I can do this in one formula and not 3?

I need to use Monday as Day1 of the week

StartDate - 09/09/2010 11:18:51 (cell C2)
EndDate - 06/09/2010 10:49:02 (cell L2)

RevisedStartDate - IF(WEEKDAY(C2,2)=7,C2+1,IF(WEEKDAY(C2,2)=6,C2+2,C2))
RevisedEndDate - IF(WEEKDAY(L2,2)=7,L2-2,IF(WEEKDAY(L2,2)=6,L2-1,L2))

DaysDifference
IF(NETWORKDAYS(O2,N2,PublicHolidays!B5:B28)>=0,(NETWORKDAYS(O2,N2,PublicHolidays!B5:B28)-1),(NETWORKDAYS(O2,N2,PublicHolidays!B5:B28))+1)

Another issue I have is that if I call a range for the PublicHolidays it returns #N/A.

How can I do this?

**daddylonglegs** · 11-02-2010, 08:56 AM

I assume the revised start date is O2 (or is it N2)? For O2 try

=NETWORKDAYS(WORKDAY(C2-1,1,holidays),WORKDAY(L2+1,-1,holidays),holidays)-SIGN(NETWORKDAYS(WORKDAY(C2-1,1,holidays),WORKDAY(L2+1,-1,holidays),holidays))

where holidays is a named range containing the holiday dates

**JezLisle** · 11-02-2010, 10:51 AM

Originally Posted by daddylonglegs

I assume the revised start date is O2 (or is it N2)? For O2 try

=NETWORKDAYS(WORKDAY(C2-1,1,holidays),WORKDAY(L2+1,-1,holidays),holidays)-SIGN(NETWORKDAYS(WORKDAY(C2-1,1,holidays),WORKDAY(L2+1,-1,holidays),holidays))

where holidays is a named range containing the holiday dates

Yes O2 was the Revised StartDate & N2 is the Revised EndDate

**JezLisle** · 11-02-2010, 11:01 AM

Excellent thanks for this its worked a treat!! :-)

**JezLisle** · 11-04-2010, 05:20 AM

I have been using the formula for a couple days now. I thought it was doing what I was looking at, but have an issue with it. In the attached example I have put my old way with the new formula and in this you can see a difference between the dates... The formula is often showing there is no difference between the dates when there is

RefID OrderTaken DelDate Revised Start Revised End Network Days NewCalc
74388 20/10/2010 20/10/2010 20/10/2010 20/10/2010 0 0
71508 06/10/2010 07/10/2010 06/10/2010 07/10/2010 1 0

How can I rectify this?