find the date of last week's wednesday

[daveyc18]

so every wed, I send out an e-mail

so I'll be sending out an e-mail Feb 13 (wednesday), but the report is actually spanning from Feb 6 (last week's wed) to Feb 12. note that if last week's wed was a holiday, it would be from Feb 7 (Thur) to Feb 12.



I've already made a table to take into account for holidays.

so, essentially, i'm just trying to find a formula to find the date of last week's wednesday, taking nto account any holidays.

 

[Dave Patton]

Excel 2010
	A	B
2		Wed Feb 6, 2019
3
4	13-Feb-19	Wed Feb 6, 2019
5
2e

Cell Formulas
Range	Formula
B2	=WORKDAY.INTL(TODAY(),-1,"1101111",rHol)
B4	=WORKDAY.INTL(A4,-1,"1101111",rHol)

Named Ranges
Name	Refers To	Cells
rHol	='2e'!$G$3:$G$6

 

[Dave Patton]

This example consider Feb 6 as a holidays per your description
"if last week's wed was a holiday, it would be from Feb 7 (Thur) "

Excel 2010
	A	B
4	13-Feb-19	Thu Feb 7, 2019
5
2e

Cell Formulas
Range	Formula
B4	=IF(COUNTIF(rHol,WORKDAY.INTL(A4,-1,"1101111")),WORKDAY.INTL(A4,-1,"1101111")+1,WORKDAY.INTL(A4,-1,"1101111"))

Named Ranges
Name	Refers To	Cells
rHol	='2e'!$G$3:$G$6

 

[daveyc18]

This example consider Feb 6 as a holidays per your description
"if last week's wed was a holiday, it would be from Feb 7 (Thur) "

Excel 2010

	A	B

<tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]13-Feb-19[/TD]
[TD="align: right"]Thu Feb 7, 2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

2e​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B4[/TH]
[TD="align: left"]=IF(COUNTIF(rHol,WORKDAY.INTL(A4,-1,"1101111")),WORKDAY.INTL(A4,-1,"1101111")+1,WORKDAY.INTL(A4,-1,"1101111"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH]rHol[/TH]
[TD="align: left"]='2e'!$G$3:$G$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

thank you

 

[daveyc18]

This example consider Feb 6 as a holidays per your description
"if last week's wed was a holiday, it would be from Feb 7 (Thur) "

Excel 2010

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]13-Feb-19[/TD]
[TD="align: right"]Thu Feb 7, 2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

2e​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=IF(COUNTIF(rHol,WORKDAY.INTL(A4,-1,"1101111")),WORKDAY.INTL(A4,-1,"1101111")+1,WORKDAY.INTL(A4,-1,"1101111"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]rHol[/TH]
[TD="align: left"]='2e'!$G$3:$G$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

****, unfortuantely, when the previous week is the previous month( instead of the current month), the formula doesnt work

eg its shows march 6 as last week

 

[daveyc18]

****, unfortuantely, when the previous week is the previous month( instead of the current month), the formula doesnt work

eg its shows march 6 as last week

oops, sorry , i meant can you adjust the formula so that it works for friday? but, again, if alst week friday is a holiday, then it falls on thrusday ...eg today is the march 8th and if march 1st was a holiday, then it'd fall on feb 28.

 

[Dave Patton]

What did you try?
Please use Excel's Evaluate and Excel's help so you see how the formula works.

Try the alternative that you prefer

Excel 2010
	A	B	C	D
6	8-Mar-19	Thu Feb 28, 2019	Thu Feb 28, 2019	Thu Feb 28, 2019
2e

Cell Formulas
Range	Formula
B6	=IF(COUNTIF(rHol,WORKDAY.INTL(A6,-1,"1111011")),WORKDAY.INTL(A6,-1,"1111011")-1,WORKDAY.INTL(A6,-1,"1111011"))
C6	=WORKDAY.INTL(A6,-1,"1111011")-COUNTIF(rHol,WORKDAY.INTL(A6,-1,"1111011"))
D6	=A6-WEEKDAY(A6-6)-COUNTIF(rHol,A6-WEEKDAY(A6-6))

Named Ranges
Name	Refers To	Cells
rHol	='2e'!$G$3:$G$8

 

[daveyc18]

What did you try?
Please use Excel's Evaluate and Excel's help so you see how the formula works.

Try the alternative that you prefer

Excel 2010

	A	B	C	D

<tbody>
[TD="align: center"]6[/TD]
[TD="align: right"]8-Mar-19[/TD]
[TD="align: right"]Thu Feb 28, 2019[/TD]
[TD="align: right"]Thu Feb 28, 2019[/TD]
[TD="align: right"]Thu Feb 28, 2019[/TD]

</tbody>

2e​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"]=IF(COUNTIF(rHol,WORKDAY.INTL(A6,-1,"1111011")),WORKDAY.INTL(A6,-1,"1111011")-1,WORKDAY.INTL(A6,-1,"1111011"))[/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=WORKDAY.INTL(A6,-1,"1111011")-COUNTIF(rHol,WORKDAY.INTL(A6,-1,"1111011"))[/TD]
[/TR]
[TR]
[TH]D6[/TH]
[TD="align: left"]=A6-WEEKDAY(A6-6)-COUNTIF(rHol,A6-WEEKDAY(A6-6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH]rHol[/TH]
[TD="align: left"]='2e'!$G$3:$G$8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

i tried nothing lol...but honestly i looked at the difference and now I understand the formula. thanks a lot