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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]2017
[/TD]
[TD]09
[/TD]
[TD]9/8/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]2017
[/TD]
[TD]10
[/TD]
[TD]10/6/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]2018
[/TD]
[TD]06
[/TD]
[TD]6/7/2018
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]2018
[/TD]
[TD]07
[/TD]
[TD]7/9/2018
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]2018
[/TD]
[TD]08
[/TD]
[TD]8/7/2018
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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