How to deduct holidays and take multiple projects into account in a report

Hi,

I recently asked about how to generate monthly and quarterly reports here: http://www.excelforum.com/excel-gene...ml#post2037946

I have now discovered that there are some problems with the validity of the "Nbr of Days worked", as it does not take into account holidays and if multiple projects are being worked on. So, if someone worked on the following:

Project A (20th December - 31st December)
Project B (1st January - 31st January)
Project C (1st January - 15th January)

This would be 8 + 22 + 11 networkdays, giving a total of 41 days. The thing is, as you can see, only 30 networkdays were actually worked upon. Then, if we take into account possible vacation days, it could be lower still.

Therefore, I need to work out a way of doing the following:

(1) Account for this overlap with the projects
(2) Be able to deduce holidays (assuming the data is provided in the file that can be manipulated).

I have attached a file that contains data that was resolved in the last post, and now includes a new tab called "holidays". In the "holidays" tab, each cell that has a "1" in it represents a day off. So, these need to be deducted from the "Nbr of Days worked" in the "report" tab.

Could you let me know if any of this is possible and how I should do this.

Thanks a lot!

island_monkey, are you able to revise the layout of your holidays data ?

If you are then first off I'd separate public holidays from vacation days... NETWORKDAYS has an optional 3rd parameter for public holidays so you can discount the generic holidays as part of the initial NETWORKDAYS function.

Once you have the above you then need to do a further calculation to subtract additional personal vacation days... for that calculation I'd be inclined to store the vacation days in a standard table rather than a matrix, ie with date stored in one column... this will keep your ranges to a minimum when you conduct the latter calculation.

If this is feasible let us know if you can revise your holiday layout.

I've taken the liberty of attaching an example to illustrate my thinking.

Note 2 new sheets, namely:

publicholidays:
contains a list of public holidays
source of dynamic named range: _publicholidays

vacation:
contains a table of vacation days (excluding public holidays)
source of dynamic named range: _vacation

The formulae on "report" sheet have thus been amended to incorporate the two new named ranges such that the resulting days are calculated exclusive of public holidays and personal vacation.

I hope that helps.

(Note if you opt for this route you can dispense with the sheet "holidays")

I was just about to ask you what you meant by that. I even removed my file and added a slightly revised version of the file with 3 tabs. I'll take a look at your file now, thanks. :-)

From a quick look, yes, I think that I should be able to transpose the holiday data into one column in a separate tab.

But, just to let you know the reason why the holidays are in this format, is because the values are copied from a different tool that stores the holiday dates. So, it just makes life easier being able to select the data from such a matrix.

island_monkey,

to be honest doing this in native formulae is probably beyond my abilities but I will flag it up to someone whom I think may be able to resolve for you.

That said I would advise that whoever looks into this to provide a solution that they use the prior example file, I would say the latest sample is a tad misleading given it has a very simplistic take on the *real* file ;-)