Hi

I myself created special 'Calendar' workbook on shared network resource (on
server).

Sheet Calendar
C1 - starting date
Row 2 - header row
Table1 (A:F, Calendar): Week, Date, Weekday, DayType, NormativeHrs, Week
Table2 (H:J, Holidays): HolidayDate, HolidayName, PreHoliday
Table3 (L, PreHolidays): PreHoliday

The PreHolidays table contains the list of holidays (holiday names), for
which the previous working day is shorter (5 hours instead 8)

In column H (Holidays.HolidayDate) are calculated all holiday dates started
from starting date until max date in column B (Calendar.Day).
In column I (Holidays.HolidayName) the name of holiday is displayed, when
HolidayDate<"".
In column J (Holidays.PreHoliday) 'TRUE' is displayed, when the holiday is
listed in PreHolidays table.

In column B (Calendar.Date) are listed dates starting from starting date (I
have prepared the table until 03.01.2011)
Column C (Calendar.Weekday) duplicates column B, but it is formatted as
"dddd"
In Column D (Calendar.DateType) are displayed codes:
for state holiday - when date exists in holidays table
for weekend - when date belongs to weekend, and it doesn't exist
in Holidays table
for pre-holiday - when date is workday, when next date exists in
Holidays table and is marked as PreHoliday
for workday - all the rest of them.
In column E (Calendar.NormativeHrs) a number 8 is displayed for workdays, 5
for pre-holudays, and 0 for rest.
In columns A and F (Calendar.Week) a week number string in format yyyy.ww is
calculated.

Sheet Weeks
This sheet contains an ODBC query from table Calendar. The cuery table
contains columns
Week, StartDate, EndDate
and has additional adjacent columns Workdays and NormativeHrs (which are
adjusted automatically, and where according values from Calendar table are
calculated)

Amongst other named ranges (used for calculations in Calendar workbook), 2
non-dynamic ranges based on tables Calendar and Weeks are defined. Whenever
some application needs any of them, I add hidden sheet(s), where tables
Calendar and/or Weeks are read into (ODBC query, direct links will be too
slow, as the source workbook contains a lot of formulas) when workbook is
opened, and from there is/are used in various calculations.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Christian (DK)" <Christian wrote in message
...
I'm trying to calculate the working hours between a lot of different
dates.
I'm currently using the networkdaysfunction to exclude weekends and
holidays.
I have two problems. Sometimes saturdays are used for working, how do I
include these? Usually, but not all the time, fridays are shorter (from
6:30
to 14:00 opposed to from 6:30 to 23:00). My current formula is only
excluding
the hours from 23:00 to 6:30.

my formula

=(NETWORKDAYS(start date; end date ;
holydaylist)-2)*16,5+((23:00-MOD(start
date;1))+(MOD(end date;1)-06:30))*24

Any help would be much appreciated.