# find the date of last week's wednesday

#### daveyc18

##### Active Member
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.

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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Dave Patton

##### Well-known Member
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) "

#### daveyc18

##### Active Member
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
AB
413-Feb-19Thu Feb 7, 2019
5

<tbody>
</tbody>
2e

Worksheet Formulas
CellFormula
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
NameRefers To
rHol='2e'!\$G\$3:\$G\$6

<tbody>
</tbody>

<tbody>
</tbody>

thank you

Last edited:

#### daveyc18

##### Active Member

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
AB
413-Feb-19Thu Feb 7, 2019
5

</tbody>
2e

Worksheet Formulas
CellFormula
B4=IF(COUNTIF(rHol,WORKDAY.INTL(A4,-1,"1101111")),WORKDAY.INTL(A4,-1,"1101111")+1,WORKDAY.INTL(A4,-1,"1101111"))

</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
rHol='2e'!\$G\$3:\$G\$6

</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

##### Active Member
****, 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

##### Well-known Member
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

Last edited:

#### daveyc18

##### Active Member
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
ABCD
68-Mar-19Thu Feb 28, 2019Thu Feb 28, 2019Thu Feb 28, 2019

<tbody>
</tbody>
2e

Worksheet Formulas
CellFormula
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
NameRefers 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

Last edited:

Replies
5
Views
145
Replies
1
Views
112
Replies
1
Views
109
Replies
3
Views
247
Replies
2
Views
176

1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back