networkingdays function

G

Guest

This is very useful but we would like to be able to vary
which day is "a nn working day" ie not necessarily
saturday or sunday.

We used to be able to do this with Lotus 123, but have now
migrated to excel.

Is there any way of achieving this please?
 
N

Norman Harker

Hi!

Myrna Larson posted this solution back in June last year:

If you need to include holidays, as you can do with NETWORKDAYS,

=NETWORKDAYS(A1+1,A2+1,HolidayRange)

This just "tricks" Excel into thinking that a Friday is really a
Saturday, a Saturday is a
Sunday, and a Sunday is a Monday, so it excludes Fridays and Saturdays
from the count, but not
Sundays.

However, it won't exclude Sundays if they are Public Holidays. If that
is an issue then I think the following works OK as long as you don't
include and Fridays in your list of Public Holidays

=NETWORKDAYS(A1+1,A2+1,HolidayRange)-SUMPRODUCT(--(HolidayRange>=A1),--(HolidayRange<=A2),--(WEEKDAY(HolidayRange)=7))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
N

Norman Harker

Hi!

Belay that version for excluding holidays. The ranges of dates and
HolidayRange are not the same.

Off to work and I'll try and work out a formula that will work and
exclude holidays properly



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/
 
N

Norman Harker

Hi!

False alarm on non-parallel SUMPRODUCT ranges. The formula does work as
described but note that the list of Holiday dates should not include Fridays
unless you want to extend the formula using:

=NETWORKDAYS(A1+1,A2+1,HolidayRange)-SUMPRODUCT(--(HolidayRange>=A1),--(Holi
dayRange<=A2),--(WEEKDAY(HolidayRange)=1))+SUMPRODUCT(--(HolidayRange>=A1),-
-(HolidayRange<=A2),--(WEEKDAY(HolidayRange)=6))

The NETWORKDAYS element calculates the days excluding Fridays and Saturdays.
The first SUMPRODUCT element deducts from that any Public Holidays that fall
on a Sunday which would otherwise be excluded from the count.
The second SUMPRODUCT element adds back any Fridays that have been listed in
the HolidayRange because they would otherwise be double-deducted.

I think that the formula can probably be made a bit more efficient but at
this stage I have concentrated on getting the logic correct.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top