PDA

View Full Version : Difficult: Sum of days in consecutive months based on multiple criteria



whimsical
07-01-2008, 08:29 AM
Hello all,

I am really hoping that someone would be kind enough to help with this as I have been trying to figure out how to do this for weeks (literally!).

I have attached the problem with some example data and the intended output table. I've tried to simplify the original problem as much as I can in order to make it easier to explain.

What I have is a list of Job Start dates and an estimate of the time (in days) for the duration of that job (i.e. either 30 or 60). What I am trying to do is calculate the number of days in each month that the job has taken. So for example, if a job will take 60 days and it starts on the 28/06, then there will be 2 days recorded in June, 31 in July and 27 in August. The sum of days per job for each month is to be calculated for each Area and Job type.
I was hoping that I could do this as a sumproduct using the existing range names, also in addition to the number of calendar days I want to do a calculation for working days as well.

I would be very thankful if someone could help and perhaps explain how a solution works.

Many many thanks!

Bob Phillips
07-01-2008, 08:46 AM
Why 2 in June, 28th, 29th, 30th makes 3 by my count.

RonMcK
07-01-2008, 09:24 AM
Maybe 'whimsical' subtracted 6/28 from 6/30 and forgot to add back 1 ?

whimsical
07-02-2008, 04:50 AM
Sorry folks I should have made my self clearer. You were right I did make an error in the example. Let me rephrase it

If a job was to last 60 days and it started on the 28-06-07 then 3 days of the total 60 day duration for that job would be counted against June (as Jun has only 30 days), 31 days for July (July has 31 days) and 26 days for August (the remainder of the 60 job length).

3 + 31 + 26 = 60 (the total duration of the job).

The summary table would then give me the total days (sum of days for all Jobs that overlap with a specific month) for each month.

Does this make sense?

Many thanks

mdmackillop
07-02-2008, 08:10 AM
I'll leave you to sort out December! Note that I have changed the month values in row 6 to formatted dates.

whimsical
07-03-2008, 11:58 AM
Excellent! many many thanks for help with this mdmackillop!
Can't thank you enough.

If I might ask further advice. Is there any way I might embed the days calc formula you provided in a summary formula (i.e. sumpoduct or sumif) that will summarise the product in the summary table (the one currently containing a count of instances).

I realise that I could populate the table with a pivot output of the data set you created. However, the original data is a database output (that includes job start date) and will cover extended periods of time (ie l will have to insert a formula field for every month in the sample).

If there any way to create a summary output of days (as per the table output) that will query the job start date relative to the table categories.

I know this does'nt work, but something like:

=SUMPRODUCT(--(AREA=$G7),--(TYPE=$H7)*(IF(L$6-$C21-SUM($I21:J21)>0,IF(L$6-$C21<=$H21,L$6-$C21-SUM($I21:J21),$H21-SUM($I21:J21)),""))

where the if statement would generate the days for each of the categories for each month.

I hope this makes sense!!! Is it impracticle to do this sort of thing with formula???

Any advice would be greatly appreciated

Once again, many thanks for your help

Bob Phillips
07-03-2008, 12:47 PM
I'm a tad confused. Isn't the table I7:T10 exactly the table that you are asking for? If not, how ould it differ, a couple of example results?

whimsical
07-04-2008, 08:01 AM
Hello xld,

Yes, I've uploaded another version of the example that illustates what the table should look like when populated wth the values from a pivot output.
The pivot table provides a sum of days (by month, Area & Type) from the formula kindly provided by 'mdmackillop'.

I'm therefore looking for a way to create the summary table values without inserting a field for every month there is a job start date.

The reason why I would be looking at a summary is that the database raw data output may span 5+ years or more. While I could probably get away with inserting a formula field for every month in the sample, this will no doubt prove quite time consuming.
I'm using a variety of other sumproduct formula to generate a number of other other outputs from the same dataset. I'm wondering if it is possible to do the same for this problem?

Many thanks for your help with this!