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