Thread: The Tueday prior to a holiday...

What happens if the Holiday is on Saturday?



"Steve" <Steve@discussions.microsoft.com> wrote in message
news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
>
> If I have holidays identified with an H in the A column, I need to find
> the
> prior Tues, from the Sat prior to the holiday.
> Basically I need to find the Sat prior to the holiday, then go back 4
> days.
> I'd like the results to show in the D column like ALERT.
>
> Thanks,
>
> Steve
>
> A B C D
> 6/28/10 Mon
> 6/29/10 Tue ALERT
> 6/30/10 Wed
> 7/1/10 Thu
> 7/2/10 Fri
> 7/3/10 Sat
> H 7/4/10 Sun
> 7/5/10 Mon
> 7/6/10 Tue
> 7/7/10 Wed
> 7/8/10 Thu
> 7/9/10 Fri
>

In those instances, I'd have to manually change the H code to Friday, and
same thing for a Sun -> Monday.

"Dennis Tucker" wrote:

> What happens if the Holiday is on Saturday?
>
>
>
> "Steve" <Steve@discussions.microsoft.com> wrote in message
> news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
> >
> > If I have holidays identified with an H in the A column, I need to find
> > the
> > prior Tues, from the Sat prior to the holiday.
> > Basically I need to find the Sat prior to the holiday, then go back 4
> > days.
> > I'd like the results to show in the D column like ALERT.
> >
> > Thanks,
> >
> > Steve
> >
> > A B C D
> > 6/28/10 Mon
> > 6/29/10 Tue ALERT
> > 6/30/10 Wed
> > 7/1/10 Thu
> > 7/2/10 Fri
> > 7/3/10 Sat
> > H 7/4/10 Sun
> > 7/5/10 Mon
> > 7/6/10 Tue
> > 7/7/10 Wed
> > 7/8/10 Thu
> > 7/9/10 Fri
> >
> .
>

I can't find/see the attachment .


"Dennis Tucker" wrote:

> See the sample file attached. Would this work?
>
>
>
> "Steve" <Steve@discussions.microsoft.com> wrote in message
> news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
> >
> > If I have holidays identified with an H in the A column, I need to find
> > the
> > prior Tues, from the Sat prior to the holiday.
> > Basically I need to find the Sat prior to the holiday, then go back 4
> > days.
> > I'd like the results to show in the D column like ALERT.
> >
> > Thanks,
> >
> > Steve
> >
> > A B C D
> > 6/28/10 Mon
> > 6/29/10 Tue ALERT
> > 6/30/10 Wed
> > 7/1/10 Thu
> > 7/2/10 Fri
> > 7/3/10 Sat
> > H 7/4/10 Sun
> > 7/5/10 Mon
> > 7/6/10 Tue
> > 7/7/10 Wed
> > 7/8/10 Thu
> > 7/9/10 Fri
> >

This isn't a binary newsgroup, so we wouldn't expect to see any attachments.
--
David Biddulph


"Steve" <Steve@discussions.microsoft.com> wrote in message
news:939C29B0-BA42-4186-814B-169EC37E675B@microsoft.com...
> I can't find/see the attachment .
>
>
> "Dennis Tucker" wrote:
>
>> See the sample file attached. Would this work?
>>
>>
>>
>> "Steve" <Steve@discussions.microsoft.com> wrote in message
>> news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
>> >
>> > If I have holidays identified with an H in the A column, I need to find
>> > the
>> > prior Tues, from the Sat prior to the holiday.
>> > Basically I need to find the Sat prior to the holiday, then go back 4
>> > days.
>> > I'd like the results to show in the D column like ALERT.
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> > A B C D
>> > 6/28/10 Mon
>> > 6/29/10 Tue ALERT
>> > 6/30/10 Wed
>> > 7/1/10 Thu
>> > 7/2/10 Fri
>> > 7/3/10 Sat
>> > H 7/4/10 Sun
>> > 7/5/10 Mon
>> > 7/6/10 Tue
>> > 7/7/10 Wed
>> > 7/8/10 Thu
>> > 7/9/10 Fri
>> >

Dennis,
Your below solution worked great.

Values
1/1/2010 6
1/2/2010 7 Saturday
1/3/2010 1
1/4/2010 2
1/5/2010 3 Tuesday ALERT

Formulas
in C2 =WEEKDAY(B2)

in D2 =IF(C2=7,"Saturday",IF(C2=3,"Tuesday",""))

in E2
=IF(AND(D2="Tuesday",OR(A6="H",A7="H",A8="H",A9="H ",A10="H",A11="H",A12="H")),"ALERT","")

Thanks again,

Steve

"Dennis Tucker" wrote:

> See the sample file attached. Would this work?
>
>
>
> "Steve" <Steve@discussions.microsoft.com> wrote in message
> news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
> >
> > If I have holidays identified with an H in the A column, I need to find
> > the
> > prior Tues, from the Sat prior to the holiday.
> > Basically I need to find the Sat prior to the holiday, then go back 4
> > days.
> > I'd like the results to show in the D column like ALERT.
> >
> > Thanks,
> >
> > Steve
> >
> > A B C D
> > 6/28/10 Mon
> > 6/29/10 Tue ALERT
> > 6/30/10 Wed
> > 7/1/10 Thu
> > 7/2/10 Fri
> > 7/3/10 Sat
> > H 7/4/10 Sun
> > 7/5/10 Mon
> > 7/6/10 Tue
> > 7/7/10 Wed
> > 7/8/10 Thu
> > 7/9/10 Fri
> >

>
=IF(AND(D2="Tuesday",OR(A6="H",A7="H",A8="H",A9="H ",A10="H",A11="H",A12="H")),"ALERT","")

Hi. Just an idea...

=IF(AND(D2="Tuesday",COUNTIF(A6:A12,"H")>0),"ALERT ","")
'or
=IF(AND(C2=2,COUNTIF(A6:A12,"H")>0),"ALERT","")

= = = = = = =
HTH :>)
Dana DeLouis



On 3/16/2010 12:02 PM, Steve wrote:
> Dennis,
> Your below solution worked great.
>
> Values
> 1/1/2010 6
> 1/2/2010 7 Saturday
> 1/3/2010 1
> 1/4/2010 2
> 1/5/2010 3 Tuesday ALERT
>
> Formulas
> in C2 =WEEKDAY(B2)
>
> in D2 =IF(C2=7,"Saturday",IF(C2=3,"Tuesday",""))
>
> in E2
> =IF(AND(D2="Tuesday",OR(A6="H",A7="H",A8="H",A9="H ",A10="H",A11="H",A12="H")),"ALERT","")
>
> Thanks again,
>
> Steve
>
> "Dennis Tucker" wrote:
>
>> See the sample file attached. Would this work?
>>
>>
>>
>> "Steve"<Steve@discussions.microsoft.com> wrote in message
>> news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
>>>
>>> If I have holidays identified with an H in the A column, I need to find
>>> the
>>> prior Tues, from the Sat prior to the holiday.
>>> Basically I need to find the Sat prior to the holiday, then go back 4
>>> days.
>>> I'd like the results to show in the D column like ALERT.
>>>
>>> Thanks,
>>>
>>> Steve
>>>
>>> A B C D
>>> 6/28/10 Mon
>>> 6/29/10 Tue ALERT
>>> 6/30/10 Wed
>>> 7/1/10 Thu
>>> 7/2/10 Fri
>>> 7/3/10 Sat
>>> H 7/4/10 Sun
>>> 7/5/10 Mon
>>> 7/6/10 Tue
>>> 7/7/10 Wed
>>> 7/8/10 Thu
>>> 7/9/10 Fri

Both of thse are producing Alerts for every Tuesday, not just the H idenfied
Tuesdays's.

"Dana DeLouis" wrote:

> >
> =IF(AND(D2="Tuesday",OR(A6="H",A7="H",A8="H",A9="H ",A10="H",A11="H",A12="H")),"ALERT","")
>
> Hi. Just an idea...
>
> =IF(AND(D2="Tuesday",COUNTIF(A6:A12,"H")>0),"ALERT ","")
> 'or
> =IF(AND(C2=2,COUNTIF(A6:A12,"H")>0),"ALERT","")
>
> = = = = = = =
> HTH :>)
> Dana DeLouis
>
>
>
> On 3/16/2010 12:02 PM, Steve wrote:
> > Dennis,
> > Your below solution worked great.
> >
> > Values
> > 1/1/2010 6
> > 1/2/2010 7 Saturday
> > 1/3/2010 1
> > 1/4/2010 2
> > 1/5/2010 3 Tuesday ALERT
> >
> > Formulas
> > in C2 =WEEKDAY(B2)
> >
> > in D2 =IF(C2=7,"Saturday",IF(C2=3,"Tuesday",""))
> >
> > in E2
> > =IF(AND(D2="Tuesday",OR(A6="H",A7="H",A8="H",A9="H ",A10="H",A11="H",A12="H")),"ALERT","")
> >
> > Thanks again,
> >
> > Steve
> >
> > "Dennis Tucker" wrote:
> >
> >> See the sample file attached. Would this work?
> >>
> >>
> >>
> >> "Steve"<Steve@discussions.microsoft.com> wrote in message
> >> news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
> >>>
> >>> If I have holidays identified with an H in the A column, I need to find
> >>> the
> >>> prior Tues, from the Sat prior to the holiday.
> >>> Basically I need to find the Sat prior to the holiday, then go back 4
> >>> days.
> >>> I'd like the results to show in the D column like ALERT.
> >>>
> >>> Thanks,
> >>>
> >>> Steve
> >>>
> >>> A B C D
> >>> 6/28/10 Mon
> >>> 6/29/10 Tue ALERT
> >>> 6/30/10 Wed
> >>> 7/1/10 Thu
> >>> 7/2/10 Fri
> >>> 7/3/10 Sat
> >>> H 7/4/10 Sun
> >>> 7/5/10 Mon
> >>> 7/6/10 Tue
> >>> 7/7/10 Wed
> >>> 7/8/10 Thu
> >>> 7/9/10 Fri
> .
>

Hi. I may have read this wrong, but it looks like to me with

OR(A6="H",A7="H",A8="H",A9="H",A10="H",A11="H",A12 ="H")

You are returning TRUE if 1 or more are "H"

I believe this also returns True if 1 or more are "H"

COUNTIF(A6:A12,"H")>0)

I don't see the error just yet... :>~

Dana DeLouis



On 3/16/10 4:19 PM, Steve wrote:
> Both of thse are producing Alerts for every Tuesday, not just the H idenfied
> Tuesdays's.
>
> "Dana DeLouis" wrote:
>
>> >
>> =IF(AND(D2="Tuesday",OR(A6="H",A7="H",A8="H",A9="H ",A10="H",A11="H",A12="H")),"ALERT","")
>>
>> Hi. Just an idea...
>>
>> =IF(AND(D2="Tuesday",COUNTIF(A6:A12,"H")>0),"ALERT ","")
>> 'or
>> =IF(AND(C2=2,COUNTIF(A6:A12,"H")>0),"ALERT","")
>>
>> = = = = = = =
>> HTH :>)
>> Dana DeLouis
>>
>>
>>
>> On 3/16/2010 12:02 PM, Steve wrote:
>>> Dennis,
>>> Your below solution worked great.
>>>
>>> Values
>>> 1/1/2010 6
>>> 1/2/2010 7 Saturday
>>> 1/3/2010 1
>>> 1/4/2010 2
>>> 1/5/2010 3 Tuesday ALERT
>>>
>>> Formulas
>>> in C2 =WEEKDAY(B2)
>>>
>>> in D2 =IF(C2=7,"Saturday",IF(C2=3,"Tuesday",""))
>>>
>>> in E2
>>> =IF(AND(D2="Tuesday",OR(A6="H",A7="H",A8="H",A9="H ",A10="H",A11="H",A12="H")),"ALERT","")
>>>
>>> Thanks again,
>>>
>>> Steve
>>>
>>> "Dennis Tucker" wrote:
>>>
>>>> See the sample file attached. Would this work?
>>>>
>>>>
>>>>
>>>> "Steve"<Steve@discussions.microsoft.com> wrote in message
>>>> news:79837A85-8096-44EC-A06A-2C3647D0B2F4@microsoft.com...
>>>>>
>>>>> If I have holidays identified with an H in the A column, I need to find
>>>>> the
>>>>> prior Tues, from the Sat prior to the holiday.
>>>>> Basically I need to find the Sat prior to the holiday, then go back 4
>>>>> days.
>>>>> I'd like the results to show in the D column like ALERT.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Steve
>>>>>
>>>>> A B C D
>>>>> 6/28/10 Mon
>>>>> 6/29/10 Tue ALERT
>>>>> 6/30/10 Wed
>>>>> 7/1/10 Thu
>>>>> 7/2/10 Fri
>>>>> 7/3/10 Sat
>>>>> H 7/4/10 Sun
>>>>> 7/5/10 Mon
>>>>> 7/6/10 Tue
>>>>> 7/7/10 Wed
>>>>> 7/8/10 Thu
>>>>> 7/9/10 Fri
>> .
>>