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

shina67

Board Regular
Joined
Sep 18, 2014
Messages
64
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.
 

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
235
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
144
A little shorter:

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



 
Last edited:

shina67

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

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top