What working day of the month a particular date is?

benposaner

New Member
Joined
Jan 7, 2021
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi

Sure it's simple but can I use a formula to workout what working day of the month a particular date is?
Assuming Working week is Mon-Fri and Bank holidays are excluded.

For example today 7th January is 4th working day of the month. (1st is Bank Hol and 2nd & 3rd are weekend)
So 1st working day would be have been the 4th Jan, etc.

So the formula should return working day 14 for 21st Jan 2021

I've tried using WORKDAY.INTL, to also exclude weekends and Holidays, but can't figure out how use the (start_date, days) in the formula to tell me what the corresponding working day of the month would be.

Any help would be great.
Thanks.
Ben
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
networkday()

=NETWORKDAYS(DATEVALUE(1&"/"&MONTH(A1)&"/"&YEAR(A1)),A1,[Holidays])

DATEVALUE(1&"/"&MONTH(A1)&"/"&YEAR(A1))
Will pickup the first of the month
 
Solution

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,629
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
alternatives

T202101a.xlsm
ABC
17-Jan-2144
221-Jan-211414
5a
Cell Formulas
RangeFormula
B1:B2B1=NETWORKDAYS(EOMONTH(A1,-1)+1,A1,Holidays)
C1:C2C1=NETWORKDAYS.INTL(EOMONTH(A1,-1)+1,A1,1,Holidays)
Named Ranges
NameRefers ToCells
'5a'!Holidays='5a'!$M$1:$M$7B1:C2
 

Watch MrExcel Video

Forum statistics

Threads
1,127,581
Messages
5,625,611
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top