Considering Holidays
 

Hello kind helpers,

I am working on part of an application that looks at compliance with
performance standards for time entry in MS Project using extracted data with
VBA in Excel (of course.)

I have the first and last day of the week desinated as wk and ewk,
respectively.
I have a list of standard holidays for US and other countries. Depending on
the resource (person's) category (US, or specified country) I need to adjust
their expected work hours entered based on whether there is a holiday or not.

The holidays need to be entered and available for the entire year, but I
will work on that later.

What I really need here is some help on how to program the comparison of the
date of the holiday (standard format = 7/04/2006) to the week, encompassed by
wk and ewk, in question. I capture the wk and ewk this way:

wk = (Today() - 8)
wkd = (Today() - 1)
twk = wk & " - " & wkd

twk I use in an email message and as far as I know is only good for that.

If you have any suggestions or questions please let me know. I appreciate
your help.

Considering Holidays
 

=IF(AND(holiday_date=wk,holiday_date<ewk),""In"," Out")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Nicole Seibert" wrote in message
...
Hello kind helpers,

I am working on part of an application that looks at compliance with
performance standards for time entry in MS Project using extracted data
with
VBA in Excel (of course.)

I have the first and last day of the week desinated as wk and ewk,
respectively.
I have a list of standard holidays for US and other countries. Depending
on
the resource (person's) category (US, or specified country) I need to
adjust
their expected work hours entered based on whether there is a holiday or
not.

The holidays need to be entered and available for the entire year, but I
will work on that later.

What I really need here is some help on how to program the comparison of
the
date of the holiday (standard format = 7/04/2006) to the week, encompassed
by
wk and ewk, in question. I capture the wk and ewk this way:

wk = (Today() - 8)
wkd = (Today() - 1)
twk = wk & " - " & wkd

twk I use in an email message and as far as I know is only good for that.

If you have any suggestions or questions please let me know. I appreciate
your help.

Considering Holidays
 

okay, so based on your statement Mr. Phillips, I wrote:

'Fixes z for Ireland's holidays
If F(i).Value = "98 IRE" And (iholiday1 = wk And iholiday1 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday2 = wk And iholiday2 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday3 = wk And iholiday3 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday4 = wk And iholiday4 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday5 = wk And iholiday5 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday6 = wk And iholiday6 < ewk)
Then z = z - 7.5

z captures the number of work hours in a week. Ireland listed above has six
holidays and works a 7.5 hour day.

Make me wish I lived in Ireland.

Anyone see any problem with this code?

"Bob Phillips" wrote:

=IF(AND(holiday_date=wk,holiday_date<ewk),""In"," Out")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Nicole Seibert" wrote in message
...
Hello kind helpers,

I am working on part of an application that looks at compliance with
performance standards for time entry in MS Project using extracted data
with
VBA in Excel (of course.)

I have the first and last day of the week desinated as wk and ewk,
respectively.
I have a list of standard holidays for US and other countries. Depending
on
the resource (person's) category (US, or specified country) I need to
adjust
their expected work hours entered based on whether there is a holiday or
not.

The holidays need to be entered and available for the entire year, but I
will work on that later.

What I really need here is some help on how to program the comparison of
the
date of the holiday (standard format = 7/04/2006) to the week, encompassed
by
wk and ewk, in question. I capture the wk and ewk this way:

wk = (Today() - 8)
wkd = (Today() - 1)
twk = wk & " - " & wkd

twk I use in an email message and as far as I know is only good for that.

If you have any suggestions or questions please let me know. I appreciate
your help.

Considering Holidays
 

I would do this

If F(I).Value = "98 IRE" Then
If (iholiday1 = wk And iholiday1 < ewk) Or _
(iholiday2 = wk And iholiday2 < ewk) Or _
(iholiday3 = wk And iholiday3 < ewk) Or _
(iholiday4 = wk And iholiday4 < ewk) Or _
(iholiday5 = wk And iholiday5 < ewk) Or _
(iholiday6 = wk And iholiday6 < ewk) Then
z = z - 7.5
End If
End If

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Nicole Seibert" wrote in message
...
okay, so based on your statement Mr. Phillips, I wrote:

'Fixes z for Ireland's holidays
If F(i).Value = "98 IRE" And (iholiday1 = wk And iholiday1 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday2 = wk And iholiday2 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday3 = wk And iholiday3 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday4 = wk And iholiday4 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday5 = wk And iholiday5 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday6 = wk And iholiday6 < ewk)
Then z = z - 7.5

z captures the number of work hours in a week. Ireland listed above has
six
holidays and works a 7.5 hour day.

Make me wish I lived in Ireland.

Anyone see any problem with this code?

"Bob Phillips" wrote:

=IF(AND(holiday_date=wk,holiday_date<ewk),""In"," Out")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Nicole Seibert" wrote in
message
...
Hello kind helpers,

I am working on part of an application that looks at compliance with
performance standards for time entry in MS Project using extracted
data
with
VBA in Excel (of course.)

I have the first and last day of the week desinated as wk and ewk,
respectively.
I have a list of standard holidays for US and other countries.
Depending
on
the resource (person's) category (US, or specified country) I need to
adjust
their expected work hours entered based on whether there is a holiday
or
not.

The holidays need to be entered and available for the entire year, but
I
will work on that later.

What I really need here is some help on how to program the comparison
of
the
date of the holiday (standard format = 7/04/2006) to the week,
encompassed
by
wk and ewk, in question. I capture the wk and ewk this way:

wk = (Today() - 8)
wkd = (Today() - 1)
twk = wk & " - " & wkd

twk I use in an email message and as far as I know is only good for
that.

If you have any suggestions or questions please let me know. I
appreciate
your help.

Considering Holidays
 

Thanks Bob,
You were very helpul. I chose not to use your example below only because
some vacation days happen in concert and I would need 7.5 to come off for
each day. For example, Christmas day and Boxing day are the 25th and 26th
respectively. The way that I entered formula in the previous message would
accomplish this.

Thank you so much for you help.
"Bob Phillips" wrote:

I would do this

If F(I).Value = "98 IRE" Then
If (iholiday1 = wk And iholiday1 < ewk) Or _
(iholiday2 = wk And iholiday2 < ewk) Or _
(iholiday3 = wk And iholiday3 < ewk) Or _
(iholiday4 = wk And iholiday4 < ewk) Or _
(iholiday5 = wk And iholiday5 < ewk) Or _
(iholiday6 = wk And iholiday6 < ewk) Then
z = z - 7.5
End If
End If

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Nicole Seibert" wrote in message
...
okay, so based on your statement Mr. Phillips, I wrote:

'Fixes z for Ireland's holidays
If F(i).Value = "98 IRE" And (iholiday1 = wk And iholiday1 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday2 = wk And iholiday2 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday3 = wk And iholiday3 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday4 = wk And iholiday4 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday5 = wk And iholiday5 < ewk)
Then z = z - 7.5
If F(i).Value = "98 IRE" And (iholiday6 = wk And iholiday6 < ewk)
Then z = z - 7.5

z captures the number of work hours in a week. Ireland listed above has
six
holidays and works a 7.5 hour day.

Make me wish I lived in Ireland.

Anyone see any problem with this code?

"Bob Phillips" wrote:

=IF(AND(holiday_date=wk,holiday_date<ewk),""In"," Out")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Nicole Seibert" wrote in
message
...
Hello kind helpers,

I am working on part of an application that looks at compliance with
performance standards for time entry in MS Project using extracted
data
with
VBA in Excel (of course.)

I have the first and last day of the week desinated as wk and ewk,
respectively.
I have a list of standard holidays for US and other countries.
Depending
on
the resource (person's) category (US, or specified country) I need to
adjust
their expected work hours entered based on whether there is a holiday
or
not.

The holidays need to be entered and available for the entire year, but
I
will work on that later.

What I really need here is some help on how to program the comparison
of
the
date of the holiday (standard format = 7/04/2006) to the week,
encompassed
by
wk and ewk, in question. I capture the wk and ewk this way:

wk = (Today() - 8)
wkd = (Today() - 1)
twk = wk & " - " & wkd

twk I use in an email message and as far as I know is only good for
that.

If you have any suggestions or questions please let me know. I
appreciate
your help.