formula to derive working day date

[vinod9111]

Hi All,

Need your help in creating a formula which will help me populate next working days' date.
It should not include public holidays, sunday and 2nd and 4th saturday

For instance 7th June is the date, i want to keep on generating next working day date as in this case it would be 8th June, then 11th june, 12th june and so on.

regards

Vinod

 

[VBA Geek]

you can build a list of public holidays and append it to a list of 2nd and 4th saturdays

to build a list of these saturdays use the below formula in A2 and drag down

=CHOOSE(MOD(ROWS(A$2:A2)-1,2)+1,DATE(2018,QUOTIENT(ROWS(A$2:A2)-1,2)+1,1)+(7-WEEKDAY(DATE(2018,QUOTIENT(ROWS(A$2:A2)-1,2)+1,1),1))+7,A1+14)

Then:


<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=WORKDAY.INTL(<font color="Blue">$C$3,ROWS(<font color="Red">D$3:D3</font>),"0000001",$A$2:$A$41</font>)</td></tr></tbody></table></td></tr></table><br />

Book1
	A	B	C	D
1	Holidays
2	Saturday, January 13, 2018		start date	next working days
3	Saturday, January 27, 2018		Thursday, June 7, 2018	Friday, June 8, 2018
4	Saturday, February 10, 2018			Monday, June 11, 2018
5	Saturday, February 24, 2018			Tuesday, June 12, 2018
6	Saturday, March 10, 2018			Wednesday, June 13, 2018
7	Saturday, March 24, 2018			Thursday, June 14, 2018
8	Saturday, April 14, 2018			Friday, June 15, 2018
9	Saturday, April 28, 2018			Saturday, June 16, 2018
10	Saturday, May 12, 2018			Monday, June 18, 2018
11	Saturday, May 26, 2018			Tuesday, June 19, 2018
12	Saturday, June 9, 2018			Wednesday, June 20, 2018
13	Saturday, June 23, 2018			Thursday, June 21, 2018
14	Saturday, July 14, 2018			Friday, June 22, 2018
15	Saturday, July 28, 2018			Monday, June 25, 2018
16	Saturday, August 11, 2018			Tuesday, June 26, 2018
17	Saturday, August 25, 2018			Wednesday, June 27, 2018
18	Saturday, September 8, 2018			Thursday, June 28, 2018
19	Saturday, September 22, 2018			Friday, June 29, 2018
20	Saturday, October 13, 2018			Saturday, June 30, 2018
21	Saturday, October 27, 2018			Monday, July 2, 2018
22	Saturday, November 10, 2018
23	Saturday, November 24, 2018
24	Saturday, December 8, 2018
25	Saturday, December 22, 2018
26	Saturday, January 12, 2019
27	Saturday, January 26, 2019
28	Saturday, February 9, 2019
29	Saturday, February 23, 2019
30	Saturday, March 9, 2019
31	Saturday, March 23, 2019
32	Saturday, April 13, 2019
33	Saturday, April 27, 2019
34	Saturday, May 11, 2019
35	Saturday, May 25, 2019
36	Saturday, June 8, 2019
37	Saturday, June 22, 2019
38	Saturday, July 13, 2019
39	Saturday, July 27, 2019
40	Saturday, August 10, 2019
41	Saturday, August 24, 2019
Sheet4

 

[vinod9111]

Thanks VBA Geek for your prompt help, you saved my lot of time. Would you mind explaining the formula how it works.

regards,
Vinod

 

[vinod9111]

Hi VBA Geek,
Is there a formula to find out date of last working day of the week, given the same conditions that it should not include public holidy, sunday and 2nd and 4th Sunday.

regards,
Vinod

 

[Tetra201]

With a list of public holidays in Holidays and a date in a week of interest in cell A1, the following formula will return the last working day of the week under the required constraints:

=WORKDAY.INTL(WORKDAY.INTL(A1-1,1,"1111101")+1,-1,"00000"&--ISODD(INT((WORKDAY.INTL(A1-1,1,"1111101")-EOMONTH(WORKDAY.INTL(A1-1,1,"1111101"),-1)-1)/7))&1,Holidays)

 

[vinod9111]

Superb Tertra201, that's worked like a charm...

Thank you so much, you guys rock...

regards
Vinod