| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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
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 . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
The Tuesday prior to a holiday...
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 "Dana DeLouis" wrote: 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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Holiday | Excel Discussion (Misc queries) | |||
| how do I add words, holiday =1, holiday am=0.5 | Excel Worksheet Functions | |||
| Holiday Planner show holiday taken? | Excel Worksheet Functions | |||
| holiday planner | Excel Worksheet Functions | |||
| Holiday Pay | New Users to Excel | |||
Linear Mode
