Macro to Return Net Work Days by Fiscal Year

Hello,

I want to create Macro that calculates Net Work Days between two dates and that also excludes holidays, but instead of difference displayed in a lump sum, I want the correct Net Work Days (excluding Holidays as well) to be displayed under its correct Federal Fiscal Year Row Title. I want the macro to use the list (holidaylist) I created in the workbook attached “TresExample” to be used to exclude the correct holidays from the correct Federal Fiscal Year.

In the example I provide in the attached workbook I am only using 10 records to simplify the example, but realistically I could have up to 20,0000 records.

In case you don’t know the Federal Fiscal Years are the following:

FY2011 10/1/2010 9/30/2011
FY2012 10/1/2011 9/30/2012
FY2013 10/1/2012 9/30/2013
FY2014 10/1/2013 9/30/2014
FY2015 10/1/2014 9/30/2015
FY2016 10/1/2015 9/30/2016
FY2017 10/1/2016 9/30/2017
FY2018 10/1/2017 9/30/2018
FY2019 10/1/2018 9/30/2019
FY2020 10/1/2019 9/30/2020


In the workbook attached to this post I have the following worksheets:
1. Holidaylist
2. Data Before Macro is Executed
3. Data After Macro is Executed

If you need me to clarify something just let me know.

- BC

Are you interested in a formula approach at all?

If so you could put this formula in N2

=MAX(0,NETWORKDAYS(MAX($K2,DATE(RIGHT(N$1,4)-1,10,1)),MIN($L2,DATE(RIGHT(N$1,4),9,30)),holidaylist!$A$2:$A$104))

custom format N2 as 0;; and then copy across and down

Easiest way:
Define a Name, say, MyNet, and copy DDL's formula into the refers to box. Then in N2 type "=MyNet"; copy down and across.

Please Login or Register  to view this content.

Regards, TMS

VBA is not needed. Ribbon: Formulas ---> Define name
in the New Name dialog, type in your desired name for the formula, then in the "refers to box" paste in the formula in post #2. Then in N2 just type "=" with whatever name you chose for the formula.

You're welcome.

Please note I have edited the code to include the formatting of the cells.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.