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

#### KC2MF

##### Board Regular
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
ABCDEF
22000StartEndDurStart DEnd D
311-Apr15-Apr5TueSat
428-Apr10-May13FriWed
523-May31-May9TueWed
69-Jun18-Jun10FriSun
730-Jun6-Jul7FriThu
813-Jul20-Jul8ThuThu
93-Aug9-Aug7ThuWed
1018-Aug23-Aug6FriWed
111-Sep10-Sep10FriSun
1218-Sep24-Sep7MonSun

<tbody>
</tbody>
Homestands

Worksheet Formulas
CellFormula
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
HIJKLMNOP
1Y/MMarAprMayJunJulAugSepOct
22000818101413182
320011614161013123
4200212123
52003
62004
72005
82006
92007
102008
112009
122010
132011
142012
152013

<tbody>
</tbody>
Homestands
Excel 2010
RSTUVWXY
1Y/DSunMonTueWedThuFriSat
220001310812131313
32001
42002
52003
62004
72005
82006
92007
102008
112009
122010
132011
142012
152013

<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.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
B3 displayed as 11-Apr, may I know the actual value is 11-Apr-2000 in date format?

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

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
ABCDEF
1YearStartEndDurStart DEnd D
22000
311-Apr-0015-Apr-005TueSat
428-Apr-0010-May-0013FriWed
523-May-0031-May-009TueWed
69-Jun-0018-Jun-0010FriSun
730-Jun-006-Jul-007FriThu
813-Jul-0020-Jul-008ThuThu
93-Aug-009-Aug-007ThuWed
1018-Aug-0023-Aug-006FriWed
111-Sep-0010-Sep-0010FriSun
1218-Sep-0024-Sep-007MonSun

<tbody>
</tbody>
Homestands

Worksheet Formulas
CellFormula
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
ABCDEF
142001StartEndDurStart DEnd D
152-Apr5-Apr4MonThu
1617-Apr22-Apr6TueSun
1724-Apr29-Apr6TueSun
187-May13-May7MonSun
1924-May30-May7ThuWed
204-Jun7-Jun4MonThu
2112-Jun17-Jun6TueSun
2225-Jun1-Jul7MonSun
236-Jul8-Jul3FriSun
2418-Jul22-Jul5WedSun
2531-Jul6-Aug7TueMon
2614-Aug19-Aug6TueSun
2731-Aug6-Sep7FriThu
2818-Sep20-Sep3TueThu
2928-Sep30-Sep3FriSun
305-Oct7-Oct3FriSun

<tbody>
</tbody>
Homestands

Worksheet Formulas
CellFormula
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.

Last edited:
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.

Last edited:
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

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>

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))

Replies
3
Views
132
Replies
13
Views
304
Replies
6
Views
208
Replies
2
Views
141
Replies
1
Views
265

1,196,346
Messages
6,014,734
Members
441,843
Latest member
benji 71

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back