I need help from a VBA genius ! I'm dying

Here's the problem!

I have 4 date ranges: 1. beginning of the month until today's date
2. beginning of the previous month until today's date of last month.
3. previous month..
4. previous month..

I got this part figured out. Now we have different business days between the starting dates and endind dates. I need to update these days so I will have the same number of business days in all 4 month.


there might be 10, 10, 10 and 9 business days. so we need to either update the dates on the month where we have 9 business days to 10 or update the days where we have 10 to 9. we cannot minimize 1 or add 1 for example because we could have a weekend.

any help would be appreciated...Thank u

You need to load in the Analysis Toolpak (in Excel, go to the Tools menu, Add-ins, then select the Analysis Toolpak and Analysis Toolpak - VBA from the list).

This will provide additional functions for use within Excel, and in particular a function to work out the number of business days between dates.

I've assumed that the key requirement is to maintain the same number of business days within the 4 date ranges by adjusting the 2nd date in each pair (the firt date of each pair is always the 1st of the month).

Try out these formulae in a worksheet:

1. The first date range is reasonably straightforward....

Set A1 to =DATEVALUE("01/" & TEXT(NOW(),"mm/yyyy"))
(this provides the first day of the current month)

Set B1 to =NOW()
(this simply sets up today's date - format the cell appropriately)

Set C1 to =NETWORKDAYS(A1,B1)
(this calculates the number of business days between the dates)

2. The 2nd date range is derived from the first....

Set A2 to =DATEVALUE("01/" & TEXT(EDATE(NOW(),-1),"mm/yyyy"))
(this calculates the 1st day of the previous month)

Set B2 to =WORKDAY(A2,$C$1-(WEEKDAY(A2,2)<=5))
(this calculates the date in the previous month that is the identical number of business days from the beginning of the previous month)

Optionally, set C2 to =NETWORKDAYS(A2,B2) to verify that the same number of business days has been calculated.

3. The 3rd date range is derived in a similar way...

Set A3 to =DATEVALUE("01/" & TEXT(EDATE(NOW(),-2),"mm/yyyy"))
Set B3 to =WORKDAY(A3,$C$1-(WEEKDAY(A3,2)<=5))
Optionally, set C3 to =NETWORKDAYS(A3,B3)

4. Finally, the 4th date range - again similar to the above...

Set A4 to =DATEVALUE("01/" & TEXT(EDATE(NOW(),-3),"mm/yyyy"))
Set B4 to =WORKDAY(A4,$C$1-(WEEKDAY(A4,2)<=5))
Optionally, set C4 to =NETWORKDAYS(A4,B4)


All of the above avoids VBA. If you needed a solution in VBA (because perhaps you have other code as part of your project) I'm not sure if the Application.WorksheetFunction item can pick up add-in functions. But, you could leave the formulae in a worksheet (hidden if needed) and then read the results into code.