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

excel5028000

New Member
Joined
Mar 5, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


DATEDESIRED_OUTCOMENOTES
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.


1583461841281.png


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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top