if cell A is 1st on month, make cell B 2 bus days later

mamadlc

New Member
Joined
Oct 5, 2018
Messages
1
This is what my spreadsheet looks like right now

[TABLE="width: 364"]
<colgroup><col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4295;" span="4"> <tbody>[TR]
[TD="width: 121, bgcolor: transparent"]CELL K

EMAIL DEPLOYMENT phase 1

if eff date is on weekend, forward to next bus day (no holidays accounted for)

[/TD]
[TD="width: 121, bgcolor: transparent"]CELL L

EMAIL DEPLOYMENT phase 2

If phase1 date is 1st of month, phase2 date is 2 bus days after (no holidays accounted for)

[/TD]
[TD="width: 121, bgcolor: #BFBFBF"]
CELL M
Email Deployment
1. Eff day or next bus day if Eff is weekend/holiday
2. If rule #1 is a Monday, deployment is Tues.
3. If rule #1 is a holiday, deployment is Wed.
4. Never on Mon or Fri.

[/TD]
[TD="width: 121, bgcolor: silver"]CELL N

Effective date
(always 1st or 16th)

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Thu, Nov 01, 2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Thu, Nov 01, 2018[/TD]
[TD="bgcolor: transparent"]Thu, Nov 01, 2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Fri, Nov 16, 2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Tue, Nov 20, 2018[/TD]
[TD="bgcolor: transparent"]Fri, Nov 16, 2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mon, Dec 03, 2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Tue, Dec 04, 2018[/TD]
[TD="bgcolor: transparent"]Sat, Dec 01, 2018[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Mon, Dec 17, 2018[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Tue, Dec 18, 2018[/TD]
[TD="bgcolor: transparent"]Sun, Dec 16, 2018[/TD]
[/TR]
</tbody>[/TABLE]

the formula in the cell K is =IF(WORKDAY(N2-1,1,$K$30:$K$40)=N2,N2,WORKDAY(N2,1,$K$30:$K$40))

the formulat in the cell M is =IF(WEEKDAY(K2)=2,WORKDAY(K2,1,$M$30:$M$40),IF(WEEKDAY(K2)=6, WORKDAY(K2,2,$M$30:$M$40), K2))

I need formula for the cell L that adds 2 bus days to the date in cell K if that date falls on 1st of month. The formula in cell M would then change to read in date from cell L and not cell K
 

Excel Facts

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

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

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