find the date of last week's wednesday

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
474
Office Version
  1. 365
  2. 2010
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.
 

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
Joined
Feb 15, 2002
Messages
4,656
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">Wed Feb 6, 2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">13-Feb-19</td><td style="text-align: right;;">Wed Feb 6, 2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2e</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=WORKDAY.INTL(<font color="Blue">TODAY(<font color="Red"></font>),-1,"1101111",rHol</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=WORKDAY.INTL(<font color="Blue">A4,-1,"1101111",rHol</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">rHol</th><td style="text-align:left">='2e'!$G$3:$G$6</td></tr></tbody></table></td></tr></table><br />
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,656
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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) "

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">13-Feb-19</td><td style="text-align: right;;">Thu Feb 7, 2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2e</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">rHol,WORKDAY.INTL(<font color="Green">A4,-1,"1101111"</font>)</font>),WORKDAY.INTL(<font color="Red">A4,-1,"1101111"</font>)+1,WORKDAY.INTL(<font color="Red">A4,-1,"1101111"</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">rHol</th><td style="text-align:left">='2e'!$G$3:$G$6</td></tr></tbody></table></td></tr></table><br />
 

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
474
Office Version
  1. 365
  2. 2010
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
Joined
Feb 11, 2013
Messages
474
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><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"))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers 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

Active Member
Joined
Feb 11, 2013
Messages
474
Office Version
  1. 365
  2. 2010
****, 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
Joined
Feb 15, 2002
Messages
4,656
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">8-Mar-19</td><td style="text-align: right;;">Thu Feb 28, 2019</td><td style="text-align: right;;">Thu Feb 28, 2019</td><td style="text-align: right;;">Thu Feb 28, 2019</td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2e</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">rHol,WORKDAY.INTL(<font color="Green">A6,-1,"1111011"</font>)</font>),WORKDAY.INTL(<font color="Red">A6,-1,"1111011"</font>)-1,WORKDAY.INTL(<font color="Red">A6,-1,"1111011"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=WORKDAY.INTL(<font color="Blue">A6,-1,"1111011"</font>)-COUNTIF(<font color="Blue">rHol,WORKDAY.INTL(<font color="Red">A6,-1,"1111011"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">=A6-WEEKDAY(<font color="Blue">A6-6</font>)-COUNTIF(<font color="Blue">rHol,A6-WEEKDAY(<font color="Red">A6-6</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">rHol</th><td style="text-align:left">='2e'!$G$3:$G$8</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
474
Office Version
  1. 365
  2. 2010
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:

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top