|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
March 11th, 2010, 07:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
The Tueday prior to a holiday...
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 
|
|
#2
March 12th, 2010, 01:13 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
What happens if the Holiday is on Saturday?
"Steve" wrote in message ... 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
|
|
#3
March 12th, 2010, 03:31 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
See the sample file attached. Would this work?
"Steve" wrote in message ... 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
|
|
#4
March 15th, 2010, 04:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
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" wrote in message ... 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 .
|
|
#5
March 15th, 2010, 04:21 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
I can't find/see the attachment .
"Dennis Tucker" wrote: See the sample file attached. Would this work? "Steve" wrote in message ... 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
|
|
#6
March 15th, 2010, 07:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
This isn't a binary newsgroup, so we wouldn't expect to see any attachments.
-- David Biddulph "Steve" wrote in message ... I can't find/see the attachment . "Dennis Tucker" wrote: See the sample file attached. Would this work? "Steve" wrote in message ... 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
|
|
#7
March 16th, 2010, 04:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
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" wrote in message ... 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
|
|
#8
March 16th, 2010, 06:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
=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? wrote in message ... 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
|
|
#9
March 16th, 2010, 08:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
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? wrote in message ... 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 .
|
|
#10
March 16th, 2010, 08:42 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
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? wrote in message ... 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 .
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
