How to set date not occur on weekend

[shah_ir85]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Date Start
[/TD]
[TD]Punishment
[/TD]
[TD]Discount
[/TD]
[TD]Punishment
[/TD]
[TD]Date Release
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Prisoner A
[/TD]
[TD]13 Jul 17
[/TD]
[TD]90 Days
[/TD]
[TD]30 Days
[/TD]
[TD]60 Days
[/TD]
[TD]10 Sept 17
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Prisoner B
[/TD]
[TD]26 Jul 17
[/TD]
[TD]80 Days
[/TD]
[TD]27 Days
[/TD]
[TD]53 Days
[/TD]
[TD]16 Sept 17
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Dave Patton]

Excel 2010
	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	Mon 11-Sep-17
3	Prisoner B	26-Jul-17	80 Days	27 Days	53	Fri 15-Sep-17
4
4c

Cell Formulas
Range	Formula
F2	=B2+E2-(WEEKDAY(B2+E2)=1)*2-(WEEKDAY(B2+E2)=7)
F3	=B3+E3-(WEEKDAY(B3+E3)=1)*2-(WEEKDAY(B3+E3)=7)

 

[frank_AL]

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))

 

[Tetra201]

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]

Excel 2010
	A	B	C	D	E	F	G
1	Name	Date Start	Punishment	Discount	Punishment	Date Release
2	Input is text	13-Jul-17	90 Days	30 Days	60	Mon 11-Sep-17	Mon 11-Sep-17
3	Input is text	26-Jul-17	80 Days	27 Days	53	Fri 15-Sep-17	Fri 15-Sep-17
4
5	Input is number	13-Jul-17	90	30	60	Mon 11-Sep-17	Mon 11-Sep-17
6	Input is number	26-Jul-17	80	27	53	Fri 15-Sep-17	Fri 15-Sep-17
7
8	Prisoner A	13-Jul-17	90 Days	30 Days	60 Days		Mon 11-Sep-17
9	Prisoner B	26-Jul-17	80 Days	27 Days	53 Days		Fri 15-Sep-17
10
4c

Cell Formulas
Range	Formula
E2	=SUBSTITUTE(C2,"Days","")-SUBSTITUTE(D2,"Days","")
E3	=SUBSTITUTE(C3,"Days","")-SUBSTITUTE(D3,"Days","")
E5	=C5-D5
E6	=C6-D6
F2	=B2+E2-(WEEKDAY(B2+E2)=1)*2-(WEEKDAY(B2+E2)=7)
F3	=B3+E3-(WEEKDAY(B3+E3)=1)*2-(WEEKDAY(B3+E3)=7)
F5	=B5+E5-(WEEKDAY(B5+E5)=1)*2-(WEEKDAY(B5+E5)=7)
F6	=B6+E6-(WEEKDAY(B6+E6)=1)*2-(WEEKDAY(B6+E6)=7)
G2	=WORKDAY(B2+E2+1,-1)
G3	=WORKDAY(B3+E3+1,-1)
G5	=WORKDAY(B5+E5+1,-1)
G6	=WORKDAY(B6+E6+1,-1)
G8	=WORKDAY(B8+SUBSTITUTE(E8,"Days","")+1,-1)
G9	=WORKDAY(B9+SUBSTITUTE(E9,"Days","")+1,-1)

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]

... 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]

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]

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

 

[shah_ir85]

i using these formula
=WORKDAY(B+E-1,-1)
its working

 

[Dave Patton]

You may want to test your formula.
Try =WORKDAY(B4+E4,-1)