How to set date not occur on weekend

shah_ir85

New Member
Joined
May 8, 2016
Messages
39
A
B
C
D
E
F
1
Name
Date Start
Punishment
Discount
Punishment
Date Release
2
Prisoner A
13 Jul 17
90 Days
30 Days
60 Days
10 Sept 17
3
Prisoner B
26 Jul 17
80 Days
27 Days
53 Days
16 Sept 17

<tbody>
</tbody>

Hi Mate

These time i was working to make some prison data

The table as show above. My problem is on cell F2 & F3

On F Column i want to make date release is on workday.
For ex: F2 is Sunday, so the prisoner should release on Friday (8 Sep 17)
Also happen to F3, prisoner should release on Friday (15 Sep 17)

I want some help how to build formula on F Column.

Please help me if you can. tQvm
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,575
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 /><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><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Name</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Date Start</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Punishment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Discount</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Punishment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Date Release</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Prisoner A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">13-Jul-17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">90 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">30 Days</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">60</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 11-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Prisoner B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">26-Jul-17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">80 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">27 Days</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">53</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 15-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></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)">4c</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)">F2</th><td style="text-align:left">=B2+E2-(<font color="Blue">WEEKDAY(<font color="Red">B2+E2</font>)=1</font>)*2-(<font color="Blue">WEEKDAY(<font color="Red">B2+E2</font>)=7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=B3+E3-(<font color="Blue">WEEKDAY(<font color="Red">B3+E3</font>)=1</font>)*2-(<font color="Blue">WEEKDAY(<font color="Red">B3+E3</font>)=7</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

frank_AL

Active Member
Joined
Oct 30, 2015
Messages
439
Dave,

I guess we working both working on this at the same time. For some reason I tried entering your formula in my test spreadsheet but I got an "#VALUE!" error.
Now I see that you converted Column E to just be a value (e.g., 60) rather that a value and text (e.g., 60 Days).

The formula I came up with is nowhere near as simple but it worked for me:
=IF(WEEKDAY(B2+LEFT(E2,2),1)=1,B2+(LEFT(E2,2)-2),IF(WEEKDAY(B2+LEFT(E2,2),1)=7,B2+(LEFT(E2,2)-1),B2+E2))
 
Last edited:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
If you can have numbers only in column E (without the "Days"), then try this:

=WORKDAY(B2+E2,-1)

Otherwise, try this:

=WORKDAY(B2+SUBSTITUTE(E2,"Days",""),-1)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,575
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

<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 /><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><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Name</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Date Start</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Punishment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Discount</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Punishment</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Date Release</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Input is text</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">13-Jul-17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">90 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">30 Days</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">60</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 11-Sep-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 11-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Input is text</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">26-Jul-17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">80 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">27 Days</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">53</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 15-Sep-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 15-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Input is number</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">13-Jul-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">90</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">30</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">60</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 11-Sep-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 11-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Input is number</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">26-Jul-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">80</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">27</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">53</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 15-Sep-17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 15-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Prisoner A</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">13-Jul-17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">90 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">30 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">60 Days</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Mon 11-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Prisoner B</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">26-Jul-17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">80 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">27 Days</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">53 Days</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Fri 15-Sep-17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;;"></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)">4c</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)">E2</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">C2,"Days",""</font>)-SUBSTITUTE(<font color="Blue">D2,"Days",""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=B2+E2-(<font color="Blue">WEEKDAY(<font color="Red">B2+E2</font>)=1</font>)*2-(<font color="Blue">WEEKDAY(<font color="Red">B2+E2</font>)=7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=WORKDAY(<font color="Blue">B2+E2+1,-1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">C3,"Days",""</font>)-SUBSTITUTE(<font color="Blue">D3,"Days",""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F3</th><td style="text-align:left">=B3+E3-(<font color="Blue">WEEKDAY(<font color="Red">B3+E3</font>)=1</font>)*2-(<font color="Blue">WEEKDAY(<font color="Red">B3+E3</font>)=7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">=WORKDAY(<font color="Blue">B3+E3+1,-1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">=C5-D5</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F5</th><td style="text-align:left">=B5+E5-(<font color="Blue">WEEKDAY(<font color="Red">B5+E5</font>)=1</font>)*2-(<font color="Blue">WEEKDAY(<font color="Red">B5+E5</font>)=7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G5</th><td style="text-align:left">=WORKDAY(<font color="Blue">B5+E5+1,-1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E6</th><td style="text-align:left">=C6-D6</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F6</th><td style="text-align:left">=B6+E6-(<font color="Blue">WEEKDAY(<font color="Red">B6+E6</font>)=1</font>)*2-(<font color="Blue">WEEKDAY(<font color="Red">B6+E6</font>)=7</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G6</th><td style="text-align:left">=WORKDAY(<font color="Blue">B6+E6+1,-1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G8</th><td style="text-align:left">=WORKDAY(<font color="Blue">B8+SUBSTITUTE(<font color="Red">E8,"Days",""</font>)+1,-1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G9</th><td style="text-align:left">=WORKDAY(<font color="Blue">B9+SUBSTITUTE(<font color="Red">E9,"Days",""</font>)+1,-1</font>)</td></tr></tbody></table></td></tr></table><br />


I included a minor edit of Tetra201's suggested Workday formula. shah_ir85 can review the post and select the suggestion that meets his requirements.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639
... I included a minor edit of Tetra201's suggested Workday formula...
Dave,

Thank you, but with your minor edit my formula does not work as requested by the OP.

To return Friday 8-Sep-17 for the start date of 13-Jul-17 and net punishment of 60 days,
my formulas should be used as shown in Post # 4 (without the additional +1):

=WORKDAY(B2+E2,-1)

or

=WORKDAY(B2+SUBSTITUTE(E2,"Days",""),-1)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,575
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Quote "On F Column i want to make date release is on workday. For ex: F2 is Sunday, so the prisoner should release on Friday (8 Sep 17)"

N.B. Jul 13 plus 60 is Monday Sep 11. I read the requirement as Weekday; not Fri. Sep 8th. Monday is a weekday.

shah_ir85 can review the post and select the suggestion that meets his requirements.
 

shah_ir85

New Member
Joined
May 8, 2016
Messages
39
hi sorry im currently on vacation and my internet really bad...about Jul 13 + 60 days is Sunday Sep 10 because when we doing calculation with date always + or - minus 1. Coz if Jul 13 + 2 days its Jul 14...first day is Jul 13 and second day is 14 day...hope i give another explaination :) very sorry for delay. my bad :(
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,575
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You may want to test your formula.
Try =WORKDAY(B4+E4,-1)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,358
Messages
5,601,149
Members
414,431
Latest member
JustmemyselfandI

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