How to calculate monthly & quarterly reports

**island_monkey** · 02-04-2009, 09:31 AM

Hi,

I am currently trying to manipulate date ranges for monthly and quarterly reports, and am having trouble doing this. I have attached a file with an example of what I would essentially need.

I would need the "Nbr of Projects", "Nbr of Days worked", "Nbr of International Projects" and "Nbr of Local Projects" cells filled in under each respective report, based on the data in the top left.

If a project falls in two months, such as "18.12.2008 to 15.01.2009", this would be treated as 1 project for December and 1 project for January. Also, I would need only "NETWORKDAYS" included in the solution, so 11 days in this example.

Please could you let me know if this is possible.

Thanks a lot,

Chris

**Cheeky Charlie** · 02-04-2009, 10:27 AM

G26=SUMPRODUCT(($D$2:$D$22=$F26)*($B$2:$B$22<DATE(2009,2,1))*($C$2:$C$22>DATE(2008,12,31)))
G36=SUMPRODUCT(($D$2:$D$22=$F26)*($B$2:$B$22<DATE(2009,4,1))*($C$2:$C$22>DATE(2008,12,31)))

How we doing?

**Cheeky Charlie** · 02-04-2009, 10:38 AM

I26=SUMPRODUCT(($D$2:$D$22=$F26)*($B$2:$B$22<DATE(2009,2,1))*($C$2:$C$22>DATE(2008,12,31))*($A$2:$A$22="International"))
J26=SUMPRODUCT(($D$2:$D$22=$F26)*($B$2:$B$22<DATE(2009,2,1))*($C$2:$C$22>DATE(2008,12,31))*($A$2:$A$22="Local"))
(OR - J26=G26-I26 of course)

Anyone else? I saved the best to last

**island_monkey** · 02-04-2009, 11:41 AM

Wow, thanks a lot Cheeky Charlie!! Much appreciated! :-)

Were you also able to solve the network days issue for "Nbr of Days worked"?

**Cheeky Charlie** · 02-04-2009, 11:50 AM

Actually, I started, but needed to do some work...

I have this much:
=MAX(NETWORKDAYS(MAX(B2,DATE(2009,1,1)),MIN(C2,DATE(2009,1,31)))*(D2="Mr White"),0)
But it needs to run over the whole column and be summed separately as it stands. It's not easy to make the networkdays with a min/max inside into an array...

Anyone else?

CC

**DonkeyOte** · 02-04-2009, 12:11 PM

AFAIK won't be able to use NETWORKDAYS as it will not accept a range of dates as date criteria you will need to go down the route of WEEKDAY(ROW(INDIRECT etc... pretty complex...

island_monkey -- to make your life easier -- are you able to create a matrix of months of interest in Cols E onwards such that you can store values/calcs by month in the appropriate monthly column and then sum as required ?

**DonkeyOte** · 02-04-2009, 12:41 PM

island_monkey, if you are able to insert some additional columns to ease the complexity of your calcs then the attached may be of interest:

that said one of the grand-fromage's may come up with something far better mind.

**island_monkey** · 02-04-2009, 12:53 PM

Thanks for that DonkeyOte, I wasn't able to reply to you earlier. And yes, if there isn't another way, it wouldn't be a problem at all to insert additional columns into my file. I'll probably just hide them though, or put them into another tab, to avoid the original file looking too cluttered.

Thanks a lot for your help! :-)