Return a date based on it being Monday, Thursday or Friday

shina67

Board Regular
Joined
Sep 18, 2014
Messages
67
Hi,

I have a spreadsheet that gets dates manually inputted into it.
What I need is a formula to return a date 7 days on from the manual input but has to be a Monday, Thursday or Friday.
I.E. Manual input is 1/10/19 or 2/10/19 I would want the returned date to be 10/10/19. Manual input 3/10/19 would return the date 10/10/19, manual input 4/10/19 returned date would be 11/10/19.

Hope this makes sense.
I have tried using a helper column with the function WEEKDAY in it but I am having some brain freeze.

Thanks in advance for any help.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
238
Here's my formula. There may be better ones.
<b></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;">1</td><td style=";">Tue</td><td style="text-align: right;;">10/1/2019</td><td style="text-align: right;;">10/10/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Wed</td><td style="text-align: right;;">10/2/2019</td><td style="text-align: right;;">10/10/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Thu</td><td style="text-align: right;;">10/3/2019</td><td style="text-align: right;;">10/10/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Fri</td><td style="text-align: right;;">10/4/2019</td><td style="text-align: right;;">10/11/2019</td><td style=";">Fri</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Sat</td><td style="text-align: right;;">10/5/2019</td><td style="text-align: right;;">10/14/2019</td><td style=";">Mon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Sun</td><td style="text-align: right;;">10/6/2019</td><td style="text-align: right;;">10/14/2019</td><td style=";">Mon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Mon</td><td style="text-align: right;;">10/7/2019</td><td style="text-align: right;;">10/14/2019</td><td style=";">Mon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Tue</td><td style="text-align: right;;">10/8/2019</td><td style="text-align: right;;">10/17/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Wed</td><td style="text-align: right;;">10/9/2019</td><td style="text-align: right;;">10/17/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Thu</td><td style="text-align: right;;">10/10/2019</td><td style="text-align: right;;">10/17/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Fri</td><td style="text-align: right;;">10/11/2019</td><td style="text-align: right;;">10/18/2019</td><td style=";">Fri</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Sat</td><td style="text-align: right;;">10/12/2019</td><td style="text-align: right;;">10/21/2019</td><td style=";">Mon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Sun</td><td style="text-align: right;;">10/13/2019</td><td style="text-align: right;;">10/21/2019</td><td style=";">Mon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Mon</td><td style="text-align: right;;">10/14/2019</td><td style="text-align: right;;">10/21/2019</td><td style=";">Mon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Tue</td><td style="text-align: right;;">10/15/2019</td><td style="text-align: right;;">10/24/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Wed</td><td style="text-align: right;;">10/16/2019</td><td style="text-align: right;;">10/24/2019</td><td style=";">Thu</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Thu</td><td style="text-align: right;;">10/17/2019</td><td style="text-align: right;;">10/24/2019</td><td style=";">Thu</td></tr></tbody></table><p style="width:4.8em;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)">Sheet1</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)">C1</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">WEEKDAY(<font color="Green">B1+7,14</font>)<3,WEEKDAY(<font color="Green">B1+7,14</font>)=5</font>),B1+7,IF(<font color="Red">WEEKDAY(<font color="Green">B1+7,14</font>)>5,B1+7+3-WEEKDAY(<font color="Green">B1+7,12</font>),B1+7+3-WEEKDAY(<font color="Green">B1+7,16</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
182
A little shorter:

=WORKDAY.INTL(A1+6,1,"0110011")



 
Last edited:

shina67

Board Regular
Joined
Sep 18, 2014
Messages
67
Thanks for your replies. Both answers worked thank you very much
 

Watch MrExcel Video

Forum statistics

Threads
1,095,583
Messages
5,445,350
Members
405,325
Latest member
Core4

This Week's Hot Topics

Top