# Count number of occurrences of a day between two dates

#### AWatson

##### New Member
I'm trying to analyse data relating to social care services. What I need to do is identify the number of hours of care provided in a financial year.

So for example, if Client 1 receives an hour of care per day x 7 days (we work on a Monday to Sunday week) and care starts on Wednesday 16th January 2008 and ends on Thursday 31st January 2008, I can manually work out that the client received 16 hours of care.

Another example would be Client 2 receives care Mondays for 3.5 hours, and Fridays for 1.5 hours and using the same start and end dates as the example above, again I can manually work out that the client received 10 hours as there would have been 2 Mondays x 3.5 hours and 2 Fridays x 1.5 hours between the start date of 16-01-2008 and end date of 31-01-2008.

I'm looking at around 2000 clients all with varying care needs and all with different start and end dates so what I would like to be able to do is count the number of occurences of each day between the start date and end date of care (i.e. how many Mondays, how many Tuesdays etc between date 1 and date 2) Is this possible?

Thanks

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Richard Schollar

##### MrExcel MVP
If you want a formula to return the number of the specific day in question then the following will do this (assume start date is in A1, end date A2):

=INT((WEEKDAY(\$A\$1-2)-\$A\$1+\$A2)/7)

The red 2 needs to be adjusted depending on the day in question:

1...Sunday
2...Monday
3...Tuesday
4...Wednesday
5...Thursday
6...Friday
7...Saturday

Hence, in the above formula, the number of tuesdays is returned.

#### AWatson

##### New Member
That's brilliant thanks to both of you. Although very slightly different, either version of the formula works perfectly.

#### Richard Schollar

##### MrExcel MVP
And my deliberate (?!) mistake was of course that the formula returned the number of Mondays, not Tuesdays

#### AWatson

##### New Member
No problems, I had noticed (and amended accordingly) but didn't want to say!! Thanks again

#### balaji_wizard

##### New Member
How can i count number of events in July when the range has dates for the whole year?I just want to know how many records are entered for July , Sep etc.

#### Richard Schollar

##### MrExcel MVP
Hi

One way would be:

=SUMPRODUCT(--(TEXT(A2:A1000,"mmm")="Jul"))

where A2:A1000 holds your event dates. Amend "Jul" to whatever month you are interested in.

Another option would be to use a Pivot Table of your data and select Group and by months.

#### AWatson

##### New Member
Re post #1 and the solution in post #3. Is it possible to work out how many Mondays, Tuesdays etc. occur on a fortnightly basis between two dates? E.g. Start date 01-Jun-09 and end date 30-Jun-09 there would be 3 Mondays, 3 Tuesdays, 2 Wednesdays etc.

Thanks again!!

Replies
1
Views
89
Replies
0
Views
77
Replies
20
Views
298
Replies
3
Views
248
Replies
3
Views
140

1,191,501
Messages
5,986,923
Members
440,067
Latest member
Swatts1

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