MS Excel Function - Networkdays

[Nilesh Inamdar]

Dear Sir,
We are trying to workout with networkdays function uses in Excel. but it is
taking the 5 days a week ( 2 holiday in week). but my requirement is 6 days a
week. I have gone throught the documents available on net. the still unable
to find the default holiday used by the networkdays function. & how to work
out with this. Please find the formula which I am using. Kindly help me in
this matter.

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),(24*(DayEnd-DayStart)*(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),ROUND((24*(DayEnd-DayStart)),2))))))

Thanks & Regards

 

[Domenic]

See if my contribution here helps...

http://www.mrexcel.com/board2/viewtopic.php?t=114724&highlight=

 

[Myrna Larson]

There is no "default" holiday list. You must supply it.

And the built-in function assumes a 5-day workweek. If you have a 6-day
workweek, this function will not help you.

You need to calculate the number of elapsed days. From that you need to
subtract the number of regular days off (Saturdays? Sundays?) and also
subtract the number of holidays.

At www.cpearson.com you can find a formula to determine the number of Mondays
between two dates. That can be modified to check for your day off.

As for the number of holidays, let's say the starting date is in A1 and the
ending date in B1, and yourlist does not include any holidays fall on the day
off, you can use COUNTIF:

=COUNTIF(HolidayList,">="&A1)-COUNTIF(HolidayList,">"&B1)



On Fri, 26 Nov 2004 05:53:02 -0800, "Nilesh Inamdar" <Nilesh

 

[Harlan Grove]

Dear Sir,

You need to get used to including the possibility that women can and do
respond in English language newsgroups, even this one.

We are trying to workout with networkdays function uses in Excel. but it
is taking the 5 days a week ( 2 holiday in week). but my requirement is
6 days a week. I have gone throught the documents available on net. the
still unable to find the default holiday used by the networkdays function.
& how to work out with this. Please find the formula which I am using.
Kindly help me in this matter.

=IF(AND(INT(StartDT)=INT(EndDT),
NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,
ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)
+INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+(DayEnd-DayStart))
/(24*(DayEnd-DayStart))))+MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)
+(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

One complication seems to be that you're handling time within StartDT and
EndDT. NETWORKDAYS doesn't do this. Put any workday in cell A1, and

=NETWORKDAYS(A1,A1)

will return 1.

If you want NETWORKDAYS functionality for 6 workdays per week, and if, say,
Saturday were always a workday unless it was included in HolidayList, the
simplest formula would be

=NETWORKDAYS(StartDT,EndDT,HolidayList)
+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDT)&":"&INT(EndDT))))=7),
--(COUNTIF(HolidayList,ROW(INDIRECT(INT(StartDT)&":"&INT(EndDT))))=0))

If the nonworkday could vary from week to week, easiest to include the
nonworkdays in HolidayList and use the formula

=SUMPRODUCT(--(COUNTIF(HolidayList,ROW(INDIRECT(INT(StartDT)
&":"&INT(EndDT))))=0))