networkdays

[Guest]

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.

 

[Ron Rosenfeld]

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.

If I understand you correctly, you will need to have some kind of indicator
that will tell Excel which Fridays have normal vs shorter working hours, and
which Saturdays are workdays.

Whether that should be a list of dates, or if there is some special
characteristic to the dates (e.g. -- last Saturday of the month; 3rd Friday of
the month) that can be used depends on the circumstances.

With enough complexity, it might be easier to code this as a UDF in VBA.


--ron

 

[Arvi Laanemets]

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.

 

[Guest]

Good responcetime! very nice

I was thinking about making a column of all dates and writing the
corresponding workinghours in the neighbouring column.
Then use the following formula:

sum(vlookup(start_date;date_column:hour_column;2;false):vlookup(end_date;date_column:hour_column;2;false))

The problem is that vlookup returns value and not a reference which is
needed for the formula. Any way around this?

Ps. I have no knowlegde about macro's, so it could be nice to avoid them, if
at all possible...

 

[Ron Rosenfeld]

Good responcetime! very nice

I was thinking about making a column of all dates and writing the
corresponding workinghours in the neighbouring column.
Then use the following formula:

sum(vlookup(start_date;date_column:hour_column;2;false):vlookup(end_date;date_column:hour_column;2;false))

The problem is that vlookup returns value and not a reference which is
needed for the formula. Any way around this?

Assuming you only have one entry per date, you could use the MATCH function to
return the positions, and then SUM the hour_column by using something like:

=IF(StartDate>EndDate,"ERROR",SUM(OFFSET(INDIRECT(
CELL("address",HourColumn)),MATCH(StartDate,DateColumn,0)
-1,0,MATCH(EndDate,DateColumn,0)-MATCH(StartDate,DateColumn,0)+1)))

The formula assumes the first row of DateColumn and HourColumn is the first row
with data, and not a label.

The formula will give an #NA if either StartDate or EndDate is not in the list.
Changing match_type to 1 (or deleting it) will have the date default to the
previous existing date, and you'll only get #NA if StartDate is earlier than
the first date in DateColumn.

Ps. I have no knowlegde about macro's, so it could be nice to avoid them, if
at all possible...

--ron

 

[Guest]

Works very nicely. Thanks a lot.

Ps. Thanks for a good support-site. This quality is rare...

 

[Ron Rosenfeld]

You're welcome. Glad to help.

This is one of the very few support sites I frequent. I find it useful, too.

Works very nicely. Thanks a lot.

Ps. Thanks for a good support-site. This quality is rare...

--ron