Should be possible using built-in functions...

[cmalmqui]

(also posted in: microsoft.public.excel.worksheetfunctions)

I am currently writing on a worksheet breaking down my project work
into days worked per month. (I am usually on 3 to 6 week long
projects...)

Input is therefore along these lines:

ID, Start Date, End Date
A , 16/10/2005, 20/11/2005
B , 20/01/2006, 25/01/2006
C , 30/01/2006, 03/03/2006

I have been trying using the function SUMPRODUCT as a basis to create
the following:

Period , Workdays
Oct/2005, 15
Nov/2005, 20
Dec/2005, 0
Jan/2006, 6
Feb/2006, 28
Mar/2006, 3

But I am getting nowhere...
Any ideas on how to solve using built-in functions?
(Need a kick in the right direction from the pro's!)

Regards,
Christian

 

[Harlan Grove]

(e-mail address removed) wrote...

I am currently writing on a worksheet breaking down my project work
into days worked per month. (I am usually on 3 to 6 week long
projects...)

Input is therefore along these lines:

ID, Start Date, End Date
A , 16/10/2005, 20/11/2005
B , 20/01/2006, 25/01/2006
C , 30/01/2006, 03/03/2006

I have been trying using the function SUMPRODUCT as a basis to create
the following:

Period , Workdays
Oct/2005, 15
Nov/2005, 20
Dec/2005, 0
Jan/2006, 6
Feb/2006, 28
Mar/2006, 3

....

If the range containing your start dates were named StartDates and the
range containing your end dates were named EndDates, and the results
range started in, say, A11 (the Period and Workdays labels in A11 and
B11, respectively, Oct/2005 in A12, etc.), you could use the following
array formulas.

B12 [array formula]:
=SUM((ROW(INDEX($A:$A,A12-DAY(A12)+1):INDEX($A:$A,A12+31-DAY(A12+31)))

=TRANSPOSE(StartDates))*(ROW(INDEX($A:$A,A12-DAY(A12)+1)

:INDEX($A:$A,A12+31-DAY(A12+31)))<=TRANSPOSE(EndDates)))

Fill B12 down as needed. Note: this treats the start and end dates as
*inclusive*, meaning, e.g., that 16-Oct-2005 and 20-Nov-2005 are both
included in project A's time span. If so, your sample results are
incorrect. Including 16-Oct, there are 16 days between 16-Oct and
31-Oct. There are 6 days in Jan/2006 between 20-Jan and 25-Jan for
project B and 2 days also between 30-Jan and 31-Jan for project C. So
my formula returns the following results.

Period Workdays
Oct/2005 16
Nov/2005 20
Dec/2005 0
Jan/2006 8
Feb/2006 28
Mar/2006 3

 

[ChristianM]

EXCELLENT!
Works as a (magical) dream!

I don't know where you guys find the time to help us out, but I am
utterly grateful! I promise to scan these forums for posts where I can
contribute!

Indeed my sample results are incorrect, which is the reason for why I
started setting up this worksheet in the first place. Start and End
dates are inclusive indeed, and your calculations are correct according
to my calendar!

Thanks Again,
Cheers,
Christian