Works very nicely. Thanks a lot.

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

"Ron Rosenfeld" wrote:



On Fri, 18 Nov 2005 08:19:15 -0800, "Christian (DK)"
wrote:

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(StartDateEndDate,"ERROR",SUM(OFFSET(INDIRECT(
CELL("address",HourColumn)),MATCH(StartDate,DateCo lumn,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 Rosenfeld" wrote:

On Fri, 18 Nov 2005 03:05:05 -0800, "Christian (DK)" <Christian
wrote:

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


--ron