half day holiday in netwokdays?

[Onion]

I'm not sure if NETWORKDAYS function can do this or not. I
have to account for a negotiated holiday of a half day.
I'm wondering if the following solution can be modified by
the experts to account for that 4 hours? OR, does
Networkdays have that ability when factoring in the last
component of my Holiday date range?

Function GetWorkDays(StartDate As Long, EndDate As Long)
As Long
' returns the count of days between StartDate - EndDate
minus Saturdays and Sundays
Dim d As Long, dCount As Long
For d = StartDate To EndDate
If Weekday(d, vbMonday) < 6 Then
dCount = dCount + 1
End If
Next d
GetWorkDays = dCount
End Function

TIA

 

[Frank Kabel]

Hi
and there's your holiday info stored. And also you may give a specific
example of the desired output

 

[Onion]

Hi Frank,
Holiday dates are in H1:H12 range (named Holidays) and
equals 92 hours (not 96) for the year. December 23, 2005
is only 4 hours.
At the end of this year I saw that I was out 4 hours using
NETWORKDAYS. My expected output is a simple projection of
a completion date for productivity. I thought of using the
code below that you kindly supplied a while ago for a
computer that did not have NETWORKDAYS hence my post. I
could manually get around the 4 hours but is there a way
to automate?

TIA

 

[Frank Kabel]

Hi
I'm still not sure how you want to calculate as your functions returns total
days. So maybe give a calculation examnple as well

 

[Onion]

=NETWORKDAYS(C8,TODAY(),Holidays)for example, returns 23
but I require a value 22.5 days because one date in
the 'Holidays' range is only 'worth' 4 work hours.
(I have a program start date and a target completion date
entered in two cells. Entries are made for daily
production elsewhere. The target date is an anchor but my
calculated date for estimated completion changes daily and
I am try attempting to get as close to the target date as
possible.)

Hope this makes sense.

 

[Frank Kabel]

Hi
o.k. now we're getting somethere
How do you tag the entries which count only for 0.5. Lets assume the
following:
X1:X10 contains your dates with 'normal' holidays (your holiday range)
Y1:Y10 contains your holidays with only 0.5 value

Now use the following formula:
=NETWORKDAYS(C8,TODAY(),X1:X10)-SUMPRODUCT(--(COUNTIF(Y1:Y10;ROW(INDIRECT(C8&":"&TODAY())))=1);--(WEEKDAY(ROW(INDIRECT(C8&":"&TODAY()));2)<6))*0.5

 

[Bernd Plumhoff]

An example of an Excel spreadsheet which allows half days off:
http://www.sulprobil.com/html/vacation_plan.html

HTH,
Bernd

 

[Onion]

Hi,
I got an error message on entering the formula and it
turns out that 3 ; starting after Y10 needed to be changed
so I put in , and got a value. I may have done something
wrong as the formula returns 120 with Jan. 1, 2004 in C8!
=NETWORKDAYS(C8,TODAY(),Holidays) returns 251.
I know we are on the right track. Can you tell me what
happened so far?

 

[Frank Kabel]

Hi
my fault. Try:
=NETWORKDAYS(C8,TODAY(),X1:X10)-SUMPRODUCT(--(COUNTIF(Y1:Y10,ROW(INDIRECT(C8&":"&TODAY())))=1),--(WEEKDAY(ROW(INDIRECT(C8&":"&TODAY())),2)<6))*0.5

Also which values do you have in Y1:Y10?

 

[Onion]

Thanks Frank that works great!!! (250.5 is the value now!)

Y1:Y10 has but one date and it is always the Christmas eve
day. Our staff gets half day off with pay and this is not
a true statutory day off. (X1:X10 are all the standard
days off).