The Tueday prior to a holiday...


S

Steve

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
 
Ad

Advertisements

S

Steve

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

Steve

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
 
D

Dana DeLouis

=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
 
Ad

Advertisements

S

Steve

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

Dana DeLouis

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
 
Ad

Advertisements

S

Steve

Yes, they are both producing TRUEs, but the 2 suggestions you originally made
are producing, if I expand it through the entire year, 52 ALERTs for every
Tuesday.
I may not have been very clear, but The H coding was corresponding to
Holidays, so I wanted to have those alerts only on the Tuesdays prior to the
Saturdays where the holiday fell. Meaning only 10 alerts on those particular
Tuesdays, if there were 10 coded H's.

Thanks,

Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top