excel 2000 (office xp) - need help with NETWORKDAYS function !!
Itzik Teboul
Networkdays function in excel assume that the non working days are saturday
and sunday.
unfortantly in my region the non working days are friday and saturday.
is there any way to deal with that problem ??
__________________________
Itzik Teboul
itz...@netvision.net.il
Harlan Grove
>Networkdays function in excel assume that the non working days are saturday
>and sunday.
>unfortantly in my region the non working days are friday and saturday.
>is there any way to deal with that problem ??
So get clever. Dates are stored as serial integers starting with 60 = 1-Mar-1900
(date previous to this suffer from unnecessarily copying Lotus 123's famous bug
of including 29-Feb-1900 as a real date even though 1900 wasn't a leap year). So
Friday-Saturday weekends can be handled by adding 1 to your dates (turing
Fridays into Saturdays, and Saturdays into Sundays). So
=NETWORKDAYS(StartDate+1,EndDate+1,Holidays+1)
entered as an array (hold down [Ctrl] and [Shift] before pressing [Enter]).
If you have more complicated work weeks, perhaps Mondays and Fridays off, you
could use
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)),2)={2,3,4,6,7}))
-SUMPRODUCT(COUNTIF(Holidays,ROW(INDIRECT(StartDate&":"&EndDate))))
For any range of dates you're likely to be working with, this should work. Note
that this latter formula doesn't need to be entered as an array formula.
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.