find the date of last week's wednesday

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
391
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

Well-known Member
Joined
Feb 15, 2002
Messages
3,780
<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
3,780
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
391
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
391
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
391
****, 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
3,780
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
391
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:

Forum statistics

Threads
1,081,984
Messages
5,362,556
Members
400,680
Latest member
Wild Child

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top