How to set date not occur on weekend

shah_ir85

New Member
Joined
May 8, 2016
Messages
40
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Excel 2010
ABCDEF
1NameDate StartPunishmentDiscountPunishmentDate Release
2Prisoner A13-Jul-1790 Days30 Days60Mon 11-Sep-17
3Prisoner B26-Jul-1780 Days27 Days53Fri 15-Sep-17
4
4c
Cell Formulas
RangeFormula
F2=B2+E2-(WEEKDAY(B2+E2)=1)*2-(WEEKDAY(B2+E2)=7)
F3=B3+E3-(WEEKDAY(B3+E3)=1)*2-(WEEKDAY(B3+E3)=7)
 
Last edited:
Upvote 0
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:
Upvote 0
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)
 
Upvote 0

Excel 2010
ABCDEFG
1NameDate StartPunishmentDiscountPunishmentDate Release
2Input is text13-Jul-1790 Days30 Days60Mon 11-Sep-17Mon 11-Sep-17
3Input is text26-Jul-1780 Days27 Days53Fri 15-Sep-17Fri 15-Sep-17
4
5Input is number13-Jul-17903060Mon 11-Sep-17Mon 11-Sep-17
6Input is number26-Jul-17802753Fri 15-Sep-17Fri 15-Sep-17
7
8Prisoner A13-Jul-1790 Days30 Days60 DaysMon 11-Sep-17
9Prisoner B26-Jul-1780 Days27 Days53 DaysFri 15-Sep-17
10
4c
Cell Formulas
RangeFormula
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.
 
Upvote 0
... 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)
 
Upvote 0
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.
 
Upvote 0
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 :(
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top