Nth Business Day of a Month/Year

[kparadise]

Hello,

I have a column with YYYYMM in it. I am able to split that column into two columns YYYY and MM. What I am trying to do is figure out what the 5th business day of each month is.

Column C holds the results I am looking for. I want to exclude weekends and holidays from the calculation of 5th business day.

	A	B	C
1	2017	09	9/8/2017
2	2017	10	10/6/2017
3	2018	06	6/7/2018
4	2018	07	7/9/2018
5	2018	08	8/7/2018

<tbody>
</tbody>

 

[Eric W]

In C1:

=WORKDAY(DATE(A1,B1,1)-1,5)

This formula returns different results from your examples, but I think they're correct. If you want to add a list of holidays, create your list in a column, and add the reference after the 5 in the formula. You can use WORKDAY.INTL if you have different requirements for which days comprise weekends.

 

[Rick Rothstein]

=WORKDAY(DATE(A1,B1,1)-1,5)

Might as well eliminate the subtraction...

=WORKDAY(DATE(A1,B1,0),5)

 

[Dave Patton]

Excel 2010
	C	D	E	F
1		5th Business Day
2	22-Jul-18	9-Jul-18	5th working day
3	Aug 2018	7-Aug-18
4
5	Jul 2018	Date formatted to month and year
6
2c

Cell Formulas
Range	Formula
D2	=WORKDAY.INTL(C2-DAY(C2),5,1,Holidays)
D3	=WORKDAY.INTL(C3-DAY(C3),5,1,Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='2c'!$J$1:$J$12