Sum workdays by month from List of Start/End dates

[moily]

Hi there,

I have a list of start and end dates that mark when holidays were begun and
ended. Some holidays were begun at the end of one month and bridged onto the
following month (ie: 26/01/09 to 06/02/09). An example list in cells (A1:B6):

Start Date End Date
05/01/09 06/01/09
14/01/09 15/01/09
26/01/09 06/02/09
18/02/09 19/02/09
09/03/09 13/03/09

I need to find out the following using a FORMULA preferably in one column
rather than spread out over several (please NO MACRO):

I need to have a list of months showing how many days were taken in each
month (say labels months 01/01/2009 through 01/12/2009 (custom formatted as
mmmmmmmmm to show the full month name) are in cells E1:E12 and the formulas
to calculate the answers are in cells F1:F12).

One complication is that one of the set of dates bridges between January and
Feburary.

Another complication is that there are more than one items per month. This
will need to assume dates could be a max of 45 start/end date rows.

I need a formula that, for the above example, will arrive at the following:
January 7
February 7
March 5

I currently have the following formula (found on the net) which deals
brilliantly with the first complication but I can't use it as it is because
it only goes line by line therefore doesn't recognise if there are multiple
lines for a given month.

=MAX(0,NETWORKDAYS(MAX(E1,A3),MIN(DATE(YEAR(E1),MONTH(E1)+1,0),B3)))

Thank you in advance for any help!

Cheers,
Ann

 

[Ron Rosenfeld]

Hi there,

I have a list of start and end dates that mark when holidays were begun and
ended. Some holidays were begun at the end of one month and bridged onto the
following month (ie: 26/01/09 to 06/02/09). An example list in cells (A1:B6):

Start Date End Date
05/01/09 06/01/09
14/01/09 15/01/09
26/01/09 06/02/09
18/02/09 19/02/09
09/03/09 13/03/09

I need to find out the following using a FORMULA preferably in one column
rather than spread out over several (please NO MACRO):

I need to have a list of months showing how many days were taken in each
month (say labels months 01/01/2009 through 01/12/2009 (custom formatted as
mmmmmmmmm to show the full month name) are in cells E1:E12 and the formulas
to calculate the answers are in cells F1:F12).

One complication is that one of the set of dates bridges between January and
Feburary.

Another complication is that there are more than one items per month. This
will need to assume dates could be a max of 45 start/end date rows.

I need a formula that, for the above example, will arrive at the following:
January 7
February 7
March 5

I currently have the following formula (found on the net) which deals
brilliantly with the first complication but I can't use it as it is because
it only goes line by line therefore doesn't recognise if there are multiple
lines for a given month.

=MAX(0,NETWORKDAYS(MAX(E1,A3),MIN(DATE(YEAR(E1),MONTH(E1)+1,0),B3)))

Thank you in advance for any help!

Cheers,
Ann

Why must you reject a VBA solution? It would be trivial to devise one.
--ron

 

[moily]

Hi Ron,

The problems I have with VBA are several fold:

First, I'm not very skilled with code and if someone else creates it for me
it's very difficult to troubleshoot it if anything goes wrong in the
longterm. Especially since the spreadsheet this is for will not just be for
myself alone and will probably see many different editors in years to come.
Even if I was willing to brush up on my VBA skills (or lack of them) the next
person or the next or the next might not have that option.

Second, I find that if something goes wrong with a macro it is difficult to
trace but if something goes wrong with a formula it's evident straight away.
So for troubleshooting purposes formulas are more transparent in my opinion.

Third, if any changes are made to the spreadsheet in years to come the macro
also has to be ammended. For example, if a row or column is added the code
has to be updated to reflect those changes. That leads me again to my first
point about myself and my future replacements lacking in skills required for
VBA.

Fourth, I find that in running macros you have to reduce the security of the
spreadsheet. In addition, even if you delete those macros you are still left
with that annoying message everytime you open up the sheet. I was told once
on this forum that if you delete the module the message won't pop up anymore
but I've tried that and it still pops up.

Fifth, I prefer to learn more about the current functionalities of Excel
rather than simply writing a macro that would probably mimic formulas less
efficiently.

Sixth, given that I know a VBA solution isn't appropriate for my
circumstances I feel it's better to write that straight away in case people
respond with answers I can't use that deter other people from clicking on my
question assuming it's already been answered.

Cheers,
Ann

 

[Ron Rosenfeld]

Hi Ron,

The problems I have with VBA are several fold:

First, I'm not very skilled with code and if someone else creates it for me
it's very difficult to troubleshoot it if anything goes wrong in the
longterm. Especially since the spreadsheet this is for will not just be for
myself alone and will probably see many different editors in years to come.
Even if I was willing to brush up on my VBA skills (or lack of them) the next
person or the next or the next might not have that option.

Second, I find that if something goes wrong with a macro it is difficult to
trace but if something goes wrong with a formula it's evident straight away.
So for troubleshooting purposes formulas are more transparent in my opinion.

Third, if any changes are made to the spreadsheet in years to come the macro
also has to be ammended. For example, if a row or column is added the code
has to be updated to reflect those changes. That leads me again to my first
point about myself and my future replacements lacking in skills required for
VBA.

Fourth, I find that in running macros you have to reduce the security of the
spreadsheet. In addition, even if you delete those macros you are still left
with that annoying message everytime you open up the sheet. I was told once
on this forum that if you delete the module the message won't pop up anymore
but I've tried that and it still pops up.

Fifth, I prefer to learn more about the current functionalities of Excel
rather than simply writing a macro that would probably mimic formulas less
efficiently.

Sixth, given that I know a VBA solution isn't appropriate for my
circumstances I feel it's better to write that straight away in case people
respond with answers I can't use that deter other people from clicking on my
question assuming it's already been answered.

Cheers,
Ann

Hopefully someone will come up with a non-VBA solution for you.

So far as troubleshooting is concerned, I find that, for complex calculations,
it is simpler to document and maintain a VBA macro than various formulas.

Each to his own.
--ron

 

[Ron Rosenfeld]

Hi Ron,

The problems I have with VBA are several fold:

First, I'm not very skilled with code and if someone else creates it for me
it's very difficult to troubleshoot it if anything goes wrong in the
longterm. Especially since the spreadsheet this is for will not just be for
myself alone and will probably see many different editors in years to come.
Even if I was willing to brush up on my VBA skills (or lack of them) the next
person or the next or the next might not have that option.

Second, I find that if something goes wrong with a macro it is difficult to
trace but if something goes wrong with a formula it's evident straight away.
So for troubleshooting purposes formulas are more transparent in my opinion.

Third, if any changes are made to the spreadsheet in years to come the macro
also has to be ammended. For example, if a row or column is added the code
has to be updated to reflect those changes. That leads me again to my first
point about myself and my future replacements lacking in skills required for
VBA.

Fourth, I find that in running macros you have to reduce the security of the
spreadsheet. In addition, even if you delete those macros you are still left
with that annoying message everytime you open up the sheet. I was told once
on this forum that if you delete the module the message won't pop up anymore
but I've tried that and it still pops up.

Fifth, I prefer to learn more about the current functionalities of Excel
rather than simply writing a macro that would probably mimic formulas less
efficiently.

Sixth, given that I know a VBA solution isn't appropriate for my
circumstances I feel it's better to write that straight away in case people
respond with answers I can't use that deter other people from clicking on my
question assuming it's already been answered.

Cheers,
Ann

Here is a non-VBA solution, but it does not meet your other requirement of a
single cell solution.

Adjacent to your columns of Start Date and End Date, and in the same row as
your labels, set up a horizontal row of labels Jan ... Dec (e.g. in D1:O1).

This table area can be anywhere on the sheet (so it can be easily hidden) so
long as it is in the same rows as your date table.

Also, set up a named range someplace called Holidays, and enter the holiday
dates into that range.

Then use this formula:

D2:

=SUMPRODUCT((MONTH(ROW(INDIRECT(Start_Date&":"&End_Date)))=COLUMNS($A:A))*
(WEEKDAY(ROW(INDIRECT(Start_Date&":"&End_Date)),3)<5)*
ISNA(MATCH(ROW(INDIRECT(Start_Date&":"&End_Date)),Holidays,0)))

Fill across to O2.

Select D2:O2 and fill down as far as required.

================================
Now you can set up your Result table where it is convenient and the use the SUM
function for each column to display the results for each month. (Or use a
Pivot Table).
--ron