Paydays for the start of the year formula

[brianfosterblack]

I am trying to get a formula for the first payday of the year depending on a clients requirement. I have this formula to get the first Monday

=(DATE(D2,1,1)+CHOOSE(WEEKDAY(DATE(D2,1,1),2),0,6,5,4,3,2,1))

However some of my clients choose their own day of the week to do payslips and I enter the day of the week in cell D63 (It could be any day from Monday through to Friday)
Some I do the payslip every Second week in which case I would have to find that second day in the year. Can anyone help with a formula which actually uses the Weekday entered in D63 and in B63 I enter the frequency (Weekly or BIWeekly)

 

[Dave Patton]

Edit the following for your requirements.
You do not have to use named ranges.
The example also includes provision for 2nd instance etc. and holidays; exclude these parts if they are not required.

Workdays.xlsm
	A	B	C
1	Year	2021
2	Date	31-Dec-20	Exclude Holiday
3
4	Next 1st, 2nd, ... from date	DOW	Result
5	1	Monday	Mon 04-Jan-21
6	1	Tue	Tue 05-Jan-21
7	1	Wed	Wed 06-Jan-21
8	2	Mon	Mon 11-Jan-21
9	1	Friday	Fri 08-Jan-21
10
1d

Cell Formulas
Range	Formula
B2	B2	=DATE(B1,1,1)-1
C5:C9	C5	=WORKDAY.INTL($B$2,A5,REPLACE("1111111",MATCH(LEFT(B5,3),DOWS,0),1,0),Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='1d'!$F$2	C5:C9

DOWS is a named information use Name Manager ={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"} or see below
Without the named ranges =WORKDAY.INTL($B$2,A5,REPLACE("1111111",MATCH(LEFT(B5,3),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),1,0),$F$2)

Include your holiday list if you use that optional parameter.