Excel formula to return 3rd workday of a month

[rajeshm28]

Hello , I need help. i need excel to return 3rd working date of any month (my working days are monday thru saturday). so for November 2024 i would like the system to return 4th November as the third working date of the month. please help me with formula.

 

[jkpieterse]

Suppose you have the starting date in cell B1 and the number of months you need in cell B2, then this formula gives you a series of vertical dates 3rd day of the months:

=LET(dates,DATE(YEAR(B1),MONTH(B1)+SEQUENCE(B2,1,0),3),IF((WEEKDAY(dates)=1)+(WEEKDAY(dates)=7)>0,dates+1,dates))

 

[Sanjeev1976]

Hi Rajesh,

Welcome to the group.

If your date is in cell A1, you can use the below formula to get the 3rd working date of that month :

=WORKDAY.INTL(EOMONTH(A1,-1),3,11)

 

[kvsrinivasamurthy]

Try this ARRAY formula . Since version is 2010
A1=Month, B1=Year.

=DATE(B1,A1,1)+SMALL(IF(WEEKDAY(DATE(B1,A1,1)+{0,1,2,3},2)<7,{0,1,2,3},""),3)

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[rajeshm28]

Hi Rajesh,

Welcome to the group.

If your date is in cell A1, you can use the below formula to get the 3rd working date of that month :

=WORKDAY.INTL(EOMONTH(A1,-1),3,11)

Suppose you have the starting date in cell B1 and the number of months you need in cell B2, then this formula gives you a series of vertical dates 3rd day of the months:

=LET(dates,DATE(YEAR(B1),MONTH(B1)+SEQUENCE(B2,1,0),3),IF((WEEKDAY(dates)=1)+(WEEKDAY(dates)=7)>0,dates+1,dates))

thank you for your response

 

[rajeshm28]

Hi Rajesh,

Welcome to the group.

If your date is in cell A1, you can use the below formula to get the 3rd working date of that month :

=WORKDAY.INTL(EOMONTH(A1,-1),3,11)

thank you , it worked

 

[rajeshm28]

Try this ARRAY formula . Since version is 2010
A1=Month, B1=Year.

=DATE(B1,A1,1)+SMALL(IF(WEEKDAY(DATE(B1,A1,1)+{0,1,2,3},2)<7,{0,1,2,3},""),3)

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

thank you, it worked