Sum the number of days between dates by Month

  • Thread starter Thread starter GavinD
  • Start date Start date
G

GavinD

I’m trying to summarise the number of NETWORKDAYS spent on projects by month.
The data I have is as follows: (dates are in UK format)

A B C
1 Project Name Start Finish
2 Project One 22/02/2008 13/03/2008
3 Project Two 22/02/2008 06/03/2008
4 Project Three 25/02/2008 11/03/2008
5 Project Four 25/04/2008 12/05/2008
6 Project Five 09/05/2008 06/06/2008
7 Project Six 13/05/2008 27/05/2008

The result I am looking for should look like this:

9 Month Number of Days
10 January 2008 0
11 February 2008 17
12 March 2008 20
13 April 2008 4
14 May 2008 35
15 June 2008 22

I have tried various combinations of arrays using SUM, SUMIF etc but cannot
find a solution. Any advice would be appreciated.

Many thanks in advance.
 
I had created a reply but not sure whether it will get posted...

Basically you need to find the networkdays for a given project and month
between MAX(Project Start Date, First day of the Month) and MIN(Project End
Date, Last day of the month) and then sum across projects for that month and
repeat for all months.
You may use helper columns or have a one big formula...
For last day of the month use
=DATE(YEAR(A1),MONTH(A1)+1,0) for the date in A1 and
=DATE(YEAR(A1),MONTH(A1),1) for the first day of the month
 
Venturing an approach which decomposes the networkdays* calcs for each
project into adjacent month/yr cols, followed by a straightforward
"transpose" summation of the calculated figs within each month/yr col further
down in the same sheet (below the projects' description part)
*w/o the holidays bit

Illustrated in this sample:
http://freefilehosting.net/download/40f2j
Summarize projects networkdays by mthyr.xls

Construct:
Startdates in C2 down, Enddates in D2 down
1st-of-month dates (formatted as "mmm-yy") listed in J1 across,
eg: Jan-08, Feb-08, etc

In J2 (all in the same cell):
=IF(TEXT(J$1,"mmm-yy")=TEXT($C2,"mmm-yy"),
networkdays($C2,DATE(YEAR(J$1),MONTH(J$1)+1,0)),
IF(TEXT(J$1,"mmm-yy")=TEXT($D2,"mmm-yy"),
networkdays(DATE(YEAR(J$1),MONTH(J$1),1),$D2),
IF(AND(DATE(YEAR(J$1),MONTH(J$1),1)>DATE(YEAR($C2),MONTH($C2),1),
DATE(YEAR(J$1),MONTH(J$1),1)<DATE(YEAR($D2),MONTH($D2),1)),
networkdays(DATE(YEAR(J$1),MONTH(J$1),1),DATE(YEAR(J$1),MONTH(J$1)+1,0)),"")))

Copy J2 across/fill down as far as required. This returns the number of
networkdays* under each month/yr's col as appropriate (between the startdates
and enddates in cols C and D)
*w/o the holidays bit

Then with the Mth/Yr "labels" listed in say, B12 down: January 2008, etc
In C12: =SUM(OFFSET($I$2:$I$9,,ROWS($1:1),))
Copy C12 down to return the required # of days

Adapt the range: $I$2:$I$9 to suit the number of rows
that is filled down for each month/yr col in col J across
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
 
hi, Gavin !

*IF* a cross-table is a valid alternate, try w/ something like the following:

- [D1] end-date starting from previous minimum-month (say 31/12/2007) and fill-right by months
- column D remains empty, start your account in...
- [E2] =sumproduct(--(weekday(row(indirect(d$1+1&":"&e$1))*isnumber(match(row(indirect(d$1+1&":"&e$1)),row(indirect($b2&":"&$c2)),0)),2)<6))

you will get network days by project (rows) & by month (columns) from [E2] to right-down (summarise as needed)

hth,
hector.

__ OP __
 
This should return all days worked if ctrl+shift+entered in a range next to
the months:

=FREQUENCY(IF((Dates>=Start)*(Dates<=Finish)*(WEEKDAY(Dates,2)<6),Dates),Month-1)

Where Start, Finish and Month refer to the three ranges and date is a
horizontal array of dates in the year:

=TRANSPOSE(ROW($39448:$39813))
 
To include the holidays bit for the networkdays (which is locality specific),
assuming that these dates (the holidays for the corresponding monthyr) will
be input in col ranges directly below the 1st-of-month headers in J1 across,
eg in J11:J15 (holidays in Jan-08), K11:K15 (holidays in Feb-08), etc

You could just use this expression instead in J2
(which points to the holidays range):

=IF(TEXT(J$1,"mmm-yy")=TEXT($C2,"mmm-yy"),
networkdays($C2,DATE(YEAR(J$1),MONTH(J$1)+1,0),J$11:J$15),
IF(TEXT(J$1,"mmm-yy")=TEXT($D2,"mmm-yy"),
networkdays(DATE(YEAR(J$1),MONTH(J$1),1),$D2,J$11:J$15),
IF(AND(DATE(YEAR(J$1),MONTH(J$1),1)>DATE(YEAR($C2),MONTH($C2),1),
DATE(YEAR(J$1),MONTH(J$1),1)<DATE(YEAR($D2),MONTH($D2),1)),
networkdays(DATE(YEAR(J$1),MONTH(J$1),1),DATE(YEAR(J$1),MONTH(J$1)+1,0),J$11:J$15),"")))

Copy J2 across/fill down as before
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
---
 
hi (again), Gavin !

if you don't want/need a cross-table (by project & month possible summarise)...

- [A9] end-date starting from previous minimum-month (say 31/12/2007) and fill-down by months

- you can overimpose a custom number-format (i.e. "Month")

- [C10] starting formula (array entered CSE) to copy-down (watch for line-wrapping)

=sum(--(weekday((row(indirect(a9+1&":"&a10))>=transpose(b$2:b$7))*(row(indirect(a9+1&":"&a10))<=transpose(c$2:c$7))
*row(indirect(a9+1&":"&a10)),2)<6))

hth,
hector.

__ previous _
*IF* a cross-table is a valid alternate, try w/ something like the following:

- [D1] end-date starting from previous minimum-month (say 31/12/2007) and fill-right by months
- column D remains empty, start your account in...
- [E2] =sumproduct(--(weekday(row(indirect(d$1+1&":"&e$1))*isnumber(match(row(indirect(d$1+1&":"&e$1)),row(indirect($b2&":"&$c2)),0)),2)<6))

you will get network days by project (rows) & by month (columns) from [E2] to right-down (summarise as needed)

__ OP __
I'm trying to summarise the number of NETWORKDAYS spent on projects by month.
The data I have is as follows: (dates are in UK format)
A B C
1 Project Name Start Finish
2 Project One 22/02/2008 13/03/2008
3 Project Two 22/02/2008 06/03/2008
4 Project Three 25/02/2008 11/03/2008
5 Project Four 25/04/2008 12/05/2008
6 Project Five 09/05/2008 06/06/2008
7 Project Six 13/05/2008 27/05/2008

The result I am looking for should look like this:
9 Month Number of Days
10 January 2008 0
11 February 2008 17
12 March 2008 20
13 April 2008 4
14 May 2008 35
15 June 2008 22

I have tried various combinations of arrays using SUM, SUMIF etc but cannot find a solution.
Any advice would be appreciated...
Click to expand...
Click to expand...
 
If you want to include a range of dates for holidays called Hols:

=FREQUENCY(IF((D>=Start)*(D<=Finish)*(WEEKDAY(D,2)<6)*(1-COUNTIF(Hols,D)),D),Month-1)

where D=Dates as before, and to avoid the range changing with row
insertion/deletions you can refer to a spare worksheet e.g. h!$39448:$39813)
 
Wouldn't it be better if your response/sample solution is able to reach out
to & benefit all those readers using versions lower than Excel 2007? Just a
thought ..
 
hi (again), Gavin !

if you need to discount holidays (say... in G11:G16), still CSE formula:

[C10] =sum(--(weekday((row(indirect(a9+1&":"&a10))>=transpose(b$2:b$7))
*(row(indirect(a9+1&":"&a10))<=transpose(c$2:c$7))
*iserror(match(row(indirect(a9+1&":"&a10)),g$11:g$16,0))
*row(indirect(a9+1&":"&a10)),2)<6))

hth,
hector.

__ previous __
if you don't want/need a cross-table (by project & month possible summarise)...

- [A9] end-date starting from previous minimum-month (say 31/12/2007) and fill-down by months

- you can overimpose a custom number-format (i.e. "Month")

- [C10] starting formula (array entered CSE) to copy-down (watch for line-wrapping)

=sum(--(weekday((row(indirect(a9+1&":"&a10))>=transpose(b$2:b$7))*(row(indirect(a9+1&":"&a10))<=transpose(c$2:c$7))
*row(indirect(a9+1&":"&a10)),2)<6))

__ previous _
*IF* a cross-table is a valid alternate, try w/ something like the following:

- [D1] end-date starting from previous minimum-month (say 31/12/2007) and fill-right by months
- column D remains empty, start your account in...
- [E2] =sumproduct(--(weekday(row(indirect(d$1+1&":"&e$1))*isnumber(match(row(indirect(d$1+1&":"&e$1)),row(indirect($b2&":"&$c2)),0)),2)<6))

you will get network days by project (rows) & by month (columns) from [E2] to right-down (summarise as needed)

__ OP __
I'm trying to summarise the number of NETWORKDAYS spent on projects by month.
The data I have is as follows: (dates are in UK format)
A B C
1 Project Name Start Finish
2 Project One 22/02/2008 13/03/2008
3 Project Two 22/02/2008 06/03/2008
4 Project Three 25/02/2008 11/03/2008
5 Project Four 25/04/2008 12/05/2008
6 Project Five 09/05/2008 06/06/2008
7 Project Six 13/05/2008 27/05/2008

The result I am looking for should look like this:
9 Month Number of Days
10 January 2008 0
11 February 2008 17
12 March 2008 20
13 April 2008 4
14 May 2008 35
15 June 2008 22

I have tried various combinations of arrays using SUM, SUMIF etc but cannot find a solution.
Any advice would be appreciated...
Click to expand...
Click to expand...
Click to expand...
 
hi, Lori !

with this method (both), I'm getting differente number of days as per OP given example
(disregarding the 22 days for June) -?-

am I missing something ?

regards,
hector.

__ OP __
 
Hi Hector, thanks for the reply, this should be correct as the dates only go
as far as June 6. i like your method too but i think it needs to be offset by
1, so that using a9&":"a10-1 in the date ranges should give the same results.
 
Sorry i misread your response, i think the discrepancy is because you're
using end of month dates, and i'm using start of month dates in the month
column.
 
hi, Lori !
... i think the discrepancy is because you're using end of month dates
and i'm using start of month dates in the month column.
Click to expand...

thanks for your input, i got it working now ;)

best regards,
hector.

__ previous __
 
Hi Herbert,

Many thanks for your solution, it worked like a dream. It's far more complex
than I imagined.

Thanks to everyone else who has also contributed.

Gavin
 
Back
Top