Calculating the number of days or days within a month within yearly date ranges

[KC2MF]

I have a series of dates from 2000 to 2013 (am using 2000 as an example) and I calculated the day that each of these date ranges began and ended. The Dur stands for the Duration (inclusive) of the two dates.

Excel 2010

	A	B	C	D	E	F
2	2000	Start	End	Dur	Start D	End D
3		11-Apr	15-Apr	5	Tue	Sat
4		28-Apr	10-May	13	Fri	Wed
5		23-May	31-May	9	Tue	Wed
6		9-Jun	18-Jun	10	Fri	Sun
7		30-Jun	6-Jul	7	Fri	Thu
8		13-Jul	20-Jul	8	Thu	Thu
9		3-Aug	9-Aug	7	Thu	Wed
10		18-Aug	23-Aug	6	Fri	Wed
11		1-Sep	10-Sep	10	Fri	Sun
12		18-Sep	24-Sep	7	Mon	Sun

<tbody>
</tbody>

Homestands​

Worksheet Formulas Cell Formula D3 =C3-B3+1 E3 =TEXT(B3,"ddd") F3 =TEXT(C3,"ddd") <tbody> </tbody>

<tbody>
</tbody>

My objective is to count the number of days in each month as well as the number of days of the week that the years date ranges add up to. This is where the formulas will be sorted, the month figures for the first two years have been computed manually (but I would really like a formula to do this). The day figures are simply approximations.
Excel 2010

	H	I	J	K	L	M	N	O	P
1	Y/M	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct
2	2000		8	18	10	14	13	18	2
3	2001		16	14	16	10	13	12	3
4	2002		12					12	3
5	2003
6	2004
7	2005
8	2006
9	2007
10	2008
11	2009
12	2010
13	2011
14	2012
15	2013

<tbody>
</tbody>

Homestands​

Excel 2010

	R	S	T	U	V	W	X	Y
1	Y/D	Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	2000	13	10	8	12	13	13	13
3	2001
4	2002
5	2003
6	2004
7	2005
8	2006
9	2007
10	2008
11	2009
12	2010
13	2011
14	2012
15	2013

<tbody>
</tbody>

Homestands​

Does anyone know what I can do to make these calculations to determine the number of Mondays (or any days) within 2000 within these ranges or the number of days in August within these ranges for example. Anyone who could help me on this one would be really appreciated. Thanks in advance.

 

[alvin-chung]

B3 displayed as 11-Apr, may I know the actual value is 11-Apr-2000 in date format?

 

[barry houdini]

Are all your date ranges self-contained within a single year or do you have date ranges which span across 2 or more years?

 

[KC2MF]

All of my ranges are contained within in 1 year so no calculations need to be done within two. Also the date that is shown in the cell corresponds to the correct year. I did this to ensure the use of the proper leap year calendar as well as to get the proper weekday displays. You can verity. Here . . .

Excel 2010

	A	B	C	D	E	F
1	Year	Start	End	Dur	Start D	End D
2	2000
3		11-Apr-00	15-Apr-00	5	Tue	Sat
4		28-Apr-00	10-May-00	13	Fri	Wed
5		23-May-00	31-May-00	9	Tue	Wed
6		9-Jun-00	18-Jun-00	10	Fri	Sun
7		30-Jun-00	6-Jul-00	7	Fri	Thu
8		13-Jul-00	20-Jul-00	8	Thu	Thu
9		3-Aug-00	9-Aug-00	7	Thu	Wed
10		18-Aug-00	23-Aug-00	6	Fri	Wed
11		1-Sep-00	10-Sep-00	10	Fri	Sun
12		18-Sep-00	24-Sep-00	7	Mon	Sun

<tbody>
</tbody>

Homestands​

Worksheet Formulas Cell Formula D3 =C3-B3+1 E3 =TEXT(B3,"ddd") F3 =TEXT(C3,"ddd") <tbody> </tbody>

<tbody>
</tbody>

Here are my figures from 2001 if you'd like more data to work with. Again dates are to the corresponding year above.

Excel 2010

	A	B	C	D	E	F
14	2001	Start	End	Dur	Start D	End D
15		2-Apr	5-Apr	4	Mon	Thu
16		17-Apr	22-Apr	6	Tue	Sun
17		24-Apr	29-Apr	6	Tue	Sun
18		7-May	13-May	7	Mon	Sun
19		24-May	30-May	7	Thu	Wed
20		4-Jun	7-Jun	4	Mon	Thu
21		12-Jun	17-Jun	6	Tue	Sun
22		25-Jun	1-Jul	7	Mon	Sun
23		6-Jul	8-Jul	3	Fri	Sun
24		18-Jul	22-Jul	5	Wed	Sun
25		31-Jul	6-Aug	7	Tue	Mon
26		14-Aug	19-Aug	6	Tue	Sun
27		31-Aug	6-Sep	7	Fri	Thu
28		18-Sep	20-Sep	3	Tue	Thu
29		28-Sep	30-Sep	3	Fri	Sun
30		5-Oct	7-Oct	3	Fri	Sun

<tbody>
</tbody>

Homestands​

Worksheet Formulas Cell Formula D15 =C15-B15+1 E15 =TEXT(B15,"ddd") F15 =TEXT(C15,"ddd") <tbody> </tbody>

<tbody>
</tbody>

As you can see, the date ranges vary in duration and are rather random in placement. They also vary from beginning to end within the year by several days or so. There can also be many ranges to as little as 8. But I need a way to quantify the how many days are within a month within these ranges and how many days of each week there are in these ranges as well.

 

[Fernando Madrid]

Hi

See below. We have some differences in the number of days for each month. May - 19; June - 11

2000	Start	End	Duration	Start D	End D	g2	h2	i2	j2	k2
	11/04/2000	15/04/2000	5	martes	sábado	30/04/2000	abril	5	abril
	28/04/2000	10/05/2000	13	viernes	miércoles	30/04/2000	abril	3	mayo	10
	23/05/2000	31/05/2000	9	martes	miércoles	31/05/2000	mayo	9	mayo
	09/06/2000	18/06/2000	10	viernes	domingo	30/06/2000	junio	10	junio
	30/06/2000	06/07/2000	7	viernes	jueves	30/06/2000	junio	1	julio	6
	13/07/2000	20/07/2000	8	jueves	jueves	31/07/2000	julio	8	julio
	03/08/2000	09/08/2000	7	jueves	miércoles	31/08/2000	agosto	7	agosto
	18/08/2000	23/08/2000	6	viernes	miércoles	31/08/2000	agosto	6	agosto
	01/09/2000	10/09/2000	10	viernes	domingo	30/09/2000	septiembre	10	septiembre
	18/09/2000	24/09/2000	7	lunes	domingo	30/09/2000	septiembre	7	septiembre

<tbody>
</tbody>

Y/M	abril(C17)	mayo	junio	julio	agosto	septiembre
2000	8 (C18)	19	11	14	13	17

<tbody>
</tbody>

Formulas:
g2 = FIN.MES(B2;0) FIN.MES - EOMONTH
h2 =TEXTO(G2;"mmmm”) TEXTO -> TEXT
i2 =SI(G2>C2;+C2-B2+1;+G2-B2+1) SI -> IF
j2 =TEXTO(C2;"mmmm")
k2 = =SI(C2>G2;DIA(C2);"") DIA -> DAY


C18 =SUMA(SUMAR.SI($H$2:$H$11;C17;$I$2:$I$11);SUMAR.SI($J$2:$J$11;C17;$K$2:$K$11)) = 8
C19= SUMA(SUMAR.SI($H$2:$H$11;D17;$I$2:$I$11);SUMAR.SI($J$2:$J$11;D17;$K$2:$K$11)) = 19
or
C18 =SUMAR.SI($H$2:$H$11;D17;$I$2:$I$11)+SUMAR.SI($J$2:$J$11;D17;$K$2:$K$11)
C19 =SUMAR.SI($H$2:$H$11;D17;$I$2:$I$11)+SUMAR.SI($J$2:$J$11;D17;$K$2:$K$11)
SUMA SUM
SUMAR.SI SUMIF

Select your ranges accordingly and copy the formulas.
You can put columns g to k in another part of your spreadsheet and hide it.


According to your table sometimes we can start in an specif month but finish in a different one. That’s why I include a new column with the last day of the starting month. Example: 28/04/00 – 10/05/00
In column I, I compare your end date with the last date and in column k I calculate the number of days of the next month when we have different start and end months.
In our example: 30/04/00-28/04/00 = 3 days in april because your end date is > than the last day of april and 10 days in may (column K).

Then I finally sum the amount for each month in both columns.

 

[barry houdini]

All of my ranges are contained within in 1 year so no calculations need to be done within two.

OK, given the setup you showed in post #1 you can use this array formula in H2 to get days in each month

=SUM(IF(($B$2:$B$100<=EOMONTH(I$1&$H2,0))*($C$2:$C$100>=(I$1&$H2)+0),IF($C$2:$C$100>EOMONTH(I$1&$H2,0),EOMONTH(I$1&$H2,0),$C$2:$C$100)-IF($B$2:$B$100<(I$1&$H2)+0,(I$1&$H2),$B$2:$B$100)+1))

confirm with CTRL+SHIFT+ENTER and copy across and down - I assumed data up to row 100, change as required

For the days of the week, does it matter if it starts with Mon rather than Sun? If not then you can use this version in S2 to give the Monday count

=SUMPRODUCT((YEAR($B$2:$B$100)=$R2)+0,NETWORKDAYS.INTL($B$2:$B$100+0,$C$2:$C$100+0,SUBSTITUTE("1111111",1,0,COLUMNS($S2:S2))))

Copy across to get the other days and down as before - note that doesn't use the header values in S1:Y1

Both formulas only use the date values in columns B and C so your additional data in columns D, E and F isn't required for these calculations

 

[Fernando Madrid]

Number of Days of the week (Sundays, Saturdays.......)

A	B	C	D	E	F	G	H
2000	Start	End
	11/04/2000	15/04/2000	5	martes	sábado
	28/04/2000	10/05/2000	13	viernes	miércoles
	23/05/2000	31/05/2000	9	martes	miércoles
	09/06/2000	18/06/2000	10	viernes	domingo
	30/06/2000	06/07/2000	7	viernes	jueves
	13/07/2000	20/07/2000	8	jueves	jueves
	03/08/2000	09/08/2000	7	jueves	miércoles
	18/08/2000	23/08/2000	6	viernes	miércoles
	01/09/2000	10/09/2000	10	viernes	domingo
	18/09/2000	24/09/2000	7	lunes	domingo
	domingo	lunes	martes	miércoles	jueves	viernes	sábado
	12	10	12	12	10	13	13

<tbody>
</tbody>

I	J	K	L	M	N	O	P	Q	R	S
11/04/2000	12/04/2000	13/04/2000	14/04/2000	15/04/2000
28/04/2000	29/04/2000	30/04/2000	01/05/2000	02/05/2000	03/05/2000	04/05/2000	05/05/2000	06/05/2000	07/05/2000	08/05/2000
23/05/2000	24/05/2000	25/05/2000	26/05/2000	27/05/2000	28/05/2000	29/05/2000	30/05/2000	31/05/2000
09/06/2000	10/06/2000	11/06/2000	12/06/2000	13/06/2000	14/06/2000	15/06/2000	16/06/2000	17/06/2000	18/06/2000
30/06/2000	01/07/2000	02/07/2000	03/07/2000	04/07/2000	05/07/2000	06/07/2000
13/07/2000	14/07/2000	15/07/2000	16/07/2000	17/07/2000	18/07/2000	19/07/2000	20/07/2000
03/08/2000	04/08/2000	05/08/2000	06/08/2000	07/08/2000	08/08/2000	09/08/2000
18/08/2000	19/08/2000	20/08/2000	21/08/2000	22/08/2000	23/08/2000
01/09/2000	02/09/2000	03/09/2000	04/09/2000	05/09/2000	06/09/2000	07/09/2000	08/09/2000	09/09/2000	10/09/2000
18/09/2000	19/09/2000	20/09/2000	21/09/2000	22/09/2000	23/09/2000	24/09/2000

<tbody>
</tbody>

Formulas:
i2 =+B2
j2 =SI(I2="";"";SI((I2+1)<=$C2;I2+1;""))
k2=SI(J2="";"";SI((J2+1)<=$C2;J2+1;""))
and so on.
I have copied in my spreadsheet these formulas in 30 columns (I to AL) and in the number of rows already filled (10).
Select your number of columns and rows. With these formulas we get all the dates between start and end as you can see in the table above.

Next:

3	4	5	6	7
6	7	1	2	3	4	5	6	7	1	2	3	4
3	4	5	6	7	1	2	3	4
6	7	1	2	3	4	5	6	7	1
6	7	1	2	3	4	5
5	6	7	1	2	3	4	5
5	6	7	1	2	3	4
6	7	1	2	3	4
6	7	1	2	3	4	5	6	7	1
2	3	4	5	6	7	1

<tbody>
</tbody>

AM to BP colums: With this formula we get the number week day for each date:
Am2 = SI(I2="";"";DIASEM(I2)) = 3 ; Am3 =SI(I3="";"";DIASEM(I3)) = 6
An2= =SI(J2="";"";DIASEM(J2)) = 4 ; An3 =SI(J3="";"";DIASEM(J3)) = 7
and so on.
SI ------- IF DIASEM ----- WEEKNUM
If you prefer TEXT just include it in the formula.
Am2=TEXTO(SI(I2="";"";DIASEM(I2));"dddd")

Total number of week days:

domingo	lunes	martes	miércoles	jueves	viernes	sábado
12 (b16)	10 (c16)	12	12	10	13	13

<tbody>
</tbody>

We know there’s a number for each day of the week:

domingo	1
lunes	2
martes	3
miércoles	4
jueves	5
viernes	6
sábado	7
So: B16=CONTAR.SI($AM$2:$BP$11;1)

<tbody>
</tbody>

C16=CONTAR.SI($AM$2:$BP$11;2)
And so on.

Finally select where you want to have these new columns or hide the ones you don’t need.

I’ve checked it adding 7 days in the end date of the first row for example (22/04/00):

domingo	lunes	martes	miércoles	jueves	viernes	sábado
13	11	13	13	11	14	14

<tbody>
</tbody>

Another easy check (end date = start date). I've also copied columns I to BP in a different spreadsheet because I don't want to see them here.

2000	Start	End
	11/04/2000	11/04/2000	1	martes	martes
	28/04/2000	28/04/2000	1	viernes	viernes
	23/05/2000	23/05/2000	1	martes	martes
	09/06/2000	09/06/2000	1	viernes	viernes
	30/06/2000	30/06/2000	1	viernes	viernes
	13/07/2000	13/07/2000	1	jueves	jueves
	03/08/2000	03/08/2000	1	jueves	jueves
	18/08/2000	18/08/2000	1	viernes	viernes
	01/09/2000	01/09/2000	1	viernes	viernes
	18/09/2000	18/09/2000	1	lunes	lunes

<tbody>
</tbody>

Results:

domingo	lunes	martes	miércoles	jueves	viernes	sábado
0	1	2	0	2	5	0

<tbody>
</tbody>

 

[MikeWx]

For those of us not yet using Excel 2010 with the NETWORKDAYS.INTL function, the following array formula in S2 for Sunday calculates the correct number of days of the week in the date ranges when copied across to Y2. I put the date ranges in a calendar style spreadsheet and the day totals matched what this formula calculated. Remove the spaces before and after the less than sign. I added them so the entire formula would appear on the site.

=SUM(IF(WEEKDAY($B$3:$B$12)+MOD($D$3:$D$12,7)-1 < IF(WEEKDAY($B$3:$B$12)>COLUMN(A1),COLUMN(A1)+7,COLUMN(A1)),0,1))+SUM(INT($D$3:$D$12/7))