# Date calculation based on weekend scenerios

#### sjha

##### Active Member
Need to calculate ‘Install’ days based on scenarios. I have lists of ‘Training’ Days and ‘Install’ days should be 5 calendars prior to ‘Training’ days.

(1) if ‘Install’ date falls on Sunday then show next Monday date. i.e. My ‘Training’ date is 8/8/2009, and ‘Install’ date is 8/2/2009 but this date falls on Sunday so show 8/3/2009 which is Monday date.

(2) if ‘Install’ date falls on Saturday then show Friday date. i.e. My ‘Training’ date is 8/7/2009, and ‘Install’ date is 8/1/2009 but this date falls on Saturday so show 7/31/2009 which is Friday date.

Your help is much appreciated. Thank you.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### jdc

##### Active Member
Just to be quick, try using the WEEKDAY function and an IF statement using the Return type to either add a day or subtract.

Hope this helps

#### sjha

##### Active Member
Thank you...but can you please elaborate? Thanks!

#### sjha

##### Active Member
nevermind...I got it now. Thanks for your help.

#### sjha

##### Active Member
Actually still having an issue. I have two parts formula and I need to put them together so that this becomes one formula.

Results should be based on either or:

First formula:=IF(WEEKDAY(X5)=7,X5-1,X5)
Second formula:=IF(WEEKDAY(X5)=1,X5+1,X5)

Thank you.

#### barry houdini

##### MrExcel MVP
Hello sjha,

You say that the install day should be 5 days before training day but your examples show 6 so, with training day in A1 get install day with this formula

=A1-CHOOSE(WEEKDAY(A1),6,6,6,6,6,7,5)

Replies
2
Views
210
Replies
0
Views
133
Replies
4
Views
172
Replies
3
Views
413
Replies
7
Views
349

1,195,958
Messages
6,012,541
Members
441,709
Latest member
diurpaneus

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

### Which adblocker are you using?

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

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