Help: Nth Business Day of a Month from mm/dd/yyyy that determines nth workday/order from bank ACH debit/credit transaction dates

[excel5028000]

Hello All,

Advanced user here but baffled. Dumb founded.

I have data dump of bank transaction dates in Excel. Incoming and outgoing ACH transaction dates. I am trying to back into the dates to determine the order of the work dates--like reverse engineer sort. Let me elaborate. For instance--I have the dates shown below on cells A2, A3, A4, A5, etc. and would like the output on column B2, B3, B4, B5, etc. For the output I only want the n numerical figure that represents and designates the nth business day or work day of the month. [We can include or exclude holidays; it's really not material at the moment as I've not reached that level, but let's take holidays into consideration]. Regardless any way, to clarify further, I want to know order--like 1 for first business day of month, 2 for second business day of month, 15 for 15th business day of month, and 18 for the 18th business day of the month, and so on. For my return or outcome from the Excel script, I only want a number that indicates the nth business day of month. I hope I am verbally posing this correctly--

I am pretty good with Excel and even macro level programming; yet I have not seen my quandary posed in this form or state or manner anywhere in the internet---and I've spent many hours searching!

To be visualize what I am seeking, please see Excel chart below.

DATE	DESIRED_OUTCOME	NOTES
1/17/2020​	12	<==formula should return 12 since this is the 12th business day or work day of the month implying 1/1/2020 is a holiday.
2/12/2020​	8	<==formula should return 8 since this is the 8th business day or work day of the month.
2/6/2020​	4	<==formula should return 4 since this is the 4th business day or work day of the month.
3/11/2020​	8	<==formula should return 8 since this is the 8th business day or work day of the month.

Thank you very much in advance for your help!! I greatly appreciate!!!!

 

[Rick Rothstein]

You could use the NETWORKDAYS function (or, if your weekends are not Saturday and Sunday, the NETWORKDAYS.INTL function). Here is the formula using NETWORKDAYS...

=NETWORKDAYS(A2-DAY(A2)+1,A2,HolidayRange)

where you would replace HolidayRange with a range reference containing all of your holidays for that year.

 

[excel5028000]

Dear Mr Excel,



Excellent--Thank you Very Much Sir! Greatly appreciate!!!