Considering Holidays

[Nicole Seibert]

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.

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[Nicole Seibert]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >

[Nicole Seibert]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in
> message
> > > news:[email protected]...
> > > > 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.
> > > >
> > >
> > >
> > >
>
>
>