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
4	13-Feb-19	Thu Feb 7, 2019
5

<tbody>
</tbody>

2e​

Worksheet Formulas Cell Formula B4 =IF(COUNTIF(rHol,WORKDAY.INTL(A4,-1,"1101111")),WORKDAY.INTL(A4,-1,"1101111")+1,WORKDAY.INTL(A4,-1,"1101111")) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To rHol ='2e'!$G$3:$G$6 <tbody> </tbody>

<tbody>
</tbody>

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
4	13-Feb-19	Thu Feb 7, 2019
5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

2e​

Worksheet Formulas Cell Formula B4 =IF(COUNTIF(rHol,WORKDAY.INTL(A4,-1,"1101111")),WORKDAY.INTL(A4,-1,"1101111")+1,WORKDAY.INTL(A4,-1,"1101111")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To rHol ='2e'!$G$3:$G$6 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

****, 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
6	8-Mar-19	Thu Feb 28, 2019	Thu Feb 28, 2019	Thu Feb 28, 2019

<tbody>
</tbody>

2e​

Worksheet Formulas Cell 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)) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To rHol ='2e'!$G$3:$G$8 <tbody> </tbody>

<tbody>
</tbody>

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